SAS 9.3 do loop within proc sql select (dynamic libname with dataset name)

Question

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 libname&yymm0..dataset&yymm0. where a=1 union
 select var1, var3 from libname&yymm1..dataset&yymm1. where a=1 union
 select var1, var3 from libname&yymm2..dataset&yymm2. where a=1 union
 select var1, var3 from libname&yymm3..dataset&yymm3. where a=1 union
 select var1, var3 from libname&yymm4..dataset&yymm4. where a=1
quit;
run;

how can i do that for 0-1000?

Answer 1

In case the libname and dataset names can be ordered you can use e.g. low and high values in simple between condition:

%let mylist=;
proc sql noprint;
select catt(libname, '.', memname, '(keep= a var1 var2)') into :mylist separated by ' '
from dictionary.tables
    where libname between "lib&yymm0" and "lib&yymm11" 
        and memname between "dataset&yymm0" and "dataset&yymm11"
;
quit;

data output;
set &mylist;
where a=1;
drop a;
run;

This should be enough for &list up to 32kB. Above this size you should generate such code to a file.