Run proc SQL by chunk of macro variable -

Run proc SQL by chunk of macro variable -

i need bring together table little table big in teradata dbms. select small.a, b, c, d 4 columns macro variables, problem varables exceed buffer size times. so, googled code (http://support.sas.com/techsup/technote/ts553.html ) below run sql chunk every 105 records. have 2 problems: 1. line "file temp;" seems not working me. error is: error: insufficient authorization access /x/sas/config/lev1/sasapp/temp.dat. 2. illustration has 1 column join, while have 4 columns a-d join. can please help me? appreciate help!

%let chunk=105;

proc sql; create view uniq select unique key little order key; info _null_; file temp; set uniq end=end; if _n_ = 1 do; set "create table result as" / " select key,data" / " connection dbms" / " (select key,data" / " big key in(" / key; end; else if mod(_n_, &chunk) = 0 , not end do; set "));" //; set "insert result" / " select key, data" / " connection dbms" / "(select key,data" / "from big key in(" / key; end; else if end do; set key "));" //; end; else set key ","; run; proc sql; connect <dbms> dbms; %inc temp;

i think sas file called temp in current directory , seek write unless you've executed filename statement telling 'temp' file somewhere else. i'm guessing you've got read access not write access in current directory (i.e. in /x/sas/config/lev1/sasapp).

try running before run datastep writes sql , see if still same error:

filename temp "%sysfunc(pathname(work))/temp.sas";

this tell sas write file called temp.sas within work library - should have write access there.

as 'joining multiple columns' - sort of bring together trying do? 4 of variables a-d little dataset keys? need match on of them? have made sure indexes exist on teradata table of these variables?

update:

it lot simpler single query multiple bring together conditions - presume alternative you've discarded after testing has established unacceptably poor level of performance?

if understand correctly, want bring together when 4 keys little table match in big table. should still possible, i'm not sure how perform on teradata side.

your current code working through little dataset 105 records @ time, constructing select , insert statements using clauses along lines of where key in (row1value row2value ... row105value). using little sets of records makes more teradata utilize index, speeding query. 1 approach take obtain 4-key bring together build clauses along lines of

where (key1 = row1value , key2 = row1value , key3 = row1value , key4 = row1value) or (key1 = row2value , key2 = row2value , key3 = row2value , key4 = row2value) or ... or (key1 = row105value , key2 = row105value , key3 = row105value , key4 = row105value)

however, don't know whether teradata take advantage of indexes on big table when performing sort of query, suggest proceed caution , research on how teradata uses indexes. might find proc sql _tree , _method options useful in establishing whether indexes beingness used.

it might create more sense initial left bring together (for 100 rows @ time little dataset) on key highest proportion of distinct values in big dataset (which create best utilize of index), utilize clause find matches on other variables. or left bring together on 4 conditions. either of these approaches, utilize firstobs , obs options partition little dataset suitably little pieces, rather writing out extensive clauses.

variables macros sas proc-sql