0

i would like to join the dataset like the following by using do loop inside the proc sql: i have macro variables yymm0-yymm4; yymm0=1505; yymm11=1501.

this is the code without do-loop:

proc sql;
 create table output as
 select var1, var3 from data1 where a=1 union
 select var1, var3 from data2 where a=1 union
 select var1, var3 from data3 where a=1 union
 select var1, var3 from data4 where a=1
quit;
run;

How can i do it if i got 12 datasets which name of the datasets are data1-data12?

useR
  • 3,062
  • 10
  • 51
  • 66
  • SAS cannot reference more than a couple of dozen tables in a single SQL statement. – Tom Jul 07 '15 at 17:09

1 Answers1

2

Since SAS 9.3 I guess, you can do this:

data output;
set data: (keep = a var1 var3);
where a=1;
drop a;
run;

where data: will match all of your data1, data2...

Of course in SQL equivalent this does an union all, not union.

Before SAS 9.3 or when the list if not a simple prefix, build a macro variable to hold your list, e.g.:

%let mylist=;
proc sql noprint;
select catt(libname, '.', memname) into :mylist separated by ' '
from dictionary.tables where libname="your libname" and memname  like 'your_pattern%'
;
quit;

Then just use &mylist instead of data: .

vasja
  • 4,732
  • 13
  • 15
  • many thanks @vasja, what if the dataset name is a dynamic macro variables? like rls&yymm0..data&yymm0. 0-11 ? – useR Jul 06 '15 at 08:41
  • 1
    If you'll always change question after an answer, the old answers won't make sense – vasja Jul 06 '15 at 08:48
  • 1
    okay. i am sorry about that. maybe i should ask another question – useR Jul 06 '15 at 08:58