topic Re: sas setting table in a loop in SAS Programming

sas setting table in a loop

ger15xxhcker — Tue, 16 Jun 2020 09:22:28 GMT

Hi all!


data test; 
   input date_loop $;
   datalines;                      
20200601
20200602
20200603
20200604
20200605
20200606
20200607
20200608
20200609
20200610
20200611
;
run;

/*
I have multiple tables : work.table20200601, work.table20200602, work.table20200603 etc. 
I' d like to iterate from this test table in a loop. Something like this:

data set_tables;
set 
	work.test20200601
	work.test20200602
	work.test20200603
	work.test20200604
	work.test20200605
	work.test20200606
	work.test20200607
	work.test20200608
	work.test20200609
	work.test20200610
	work.test20200611
;
run;
*/

Thanks!

Re: sas setting table in a loop

Kurt_Bremser — Tue, 16 Jun 2020 09:34:49 GMT

The first method (and the easiest and quickest) is to use wildcards:

data set_tables;
set 
  work.test202006:
;
run;

The second is the creation of a macro variable (if the wildcard would otherwise pick up unwanted datasets):

proc sql noprint;
select 'WORK.' !! date_loop into :datasets separated by " "
from test;
quit;

data set_tables;
set &datasets.;
run;

Re: sas setting table in a loop

PeterClemmensen — Tue, 16 Jun 2020 09:40:27 GMT

If your task is to read all data sets with the prefix work.test202006, then simply use the colon operator like this

data want;
    set work.test202006: ;
run;

Otherwise, you can use Call Execute logic like this

data test; 
   input date_loop $;
   datalines;                      
20200601
20200602
20200603
20200604
20200605
20200606
20200607
20200608
20200609
20200610
20200611
;
run;

data _null_;
   set test;
   call execute ("data set_tables; set ");
   call execute (cat("work.test", date_loop));
   call execute ("; run;");
run;

Re: sas setting table in a loop

ger15xxhcker — Tue, 16 Jun 2020 09:43:10 GMT

Thanks for your help!

Re: sas setting table in a loop

Jagadishkatam — Tue, 16 Jun 2020 09:57:36 GMT

alternatively you can try the below code with do loop


data _null_;
set test nobs=obs;
call symputx("test"||strip(put(_n_,best.)),cats('test',date_loop));
call symputx("obs",strip(obs));
run;

%put &test1 &test2 &obs;

%macro test;
data want;
set
%do i = 1 %to &obs;
 &&test&i
%end;;
run;
%mend;

%test;