Hi --
I'm trying to run a macro which will loop through each value of a date in a dataset. In the below example I have 6 dates that I want to pass through a loop. I want the macro to output 6 datasets with the SAS date value added to the end of the dataset name.
For example - the dates table looks like this:
21185
21216
21244
21275
21305
21336
I want output datasets called:
test_21185
test_21216
..etc
For some reason I cannot pass the date value to the dataset name highlighted in red text below. I do not want to use a call execute for this -- has to do with some compilation time/execution time nuances for my actual application.
/*Create a dataset with dates to be used for a loop*/
data work.t0;
LOOK_AT_MTH="01JAN2018"d;
do while (LOOK_AT_MTH<="01JUN2018"d);
output;
LOOK_AT_MTH=intnx('month', LOOK_AT_MTH, 1, 's');
end;
run;
/*put dates into a macro variable pipe delimited*/
proc sql noprint;
select distinct LOOK_AT_MTH into :dates separated by '|'
from work.t0;
quit;
/*macro to loop throgh each date value -- I want the output to be test_21185, test_21216, etc...*/
%macro runloop;
%do i=1 %to %sysfunc(countw(%superq(dates),|));
%let dsname=%qscan(%superq(dates),&i,|);
data test_&dsname.;
date=%qscan(%superq(dates),&i,|);
run;
%end;
%mend runloop;
%runloop;
data work.t0;
LOOK_AT_MTH="01JAN2018"d;
do while (LOOK_AT_MTH<="01JUN2018"d);
output;
LOOK_AT_MTH=intnx('month', LOOK_AT_MTH, 1, 's');
end;
run;
/*put dates into a macro variable pipe delimited*/
proc sql noprint;
select distinct LOOK_AT_MTH into :dates separated by '|'
from work.t0;
quit;
/*macro to loop throgh each date value -- I want the output to be test_21185, test_21216, etc...*/
%macro runloop;
%do i=1 %to %sysfunc(countw(%superq(dates),|));
%let dsname=%sysfunc(compress(%qscan(%superq(dates),&i,|)));
data test_&dsname.;
date=&dsname;
run;
%put &dsname;
%end;
%mend runloop;
%runloop;
It's a little bit of a guess, since we have no log, no diagnostics, and just the clue that the process breaks down somewhere between the beginning and the end. So with that to work with ...
You are quoting things that don't need to be quoted. Get rid of %superq, change %qscan to %scan, and try it this way:
%macro runloop;
%do i=1 %to %sysfunc(countw(&dates,|));
%let dsname=%scan(&dates,&i,| |);
data test_&dsname.;
date=&dsname;
run;
%end;
%mend runloop;
Also note, using | as a delimiter can cause trouble depending on where the | is used by macro language. It is another way of saying "or" which can be interpreted as the logical "or" depending on usage.
data work.t0;
LOOK_AT_MTH="01JAN2018"d;
do while (LOOK_AT_MTH<="01JUN2018"d);
output;
LOOK_AT_MTH=intnx('month', LOOK_AT_MTH, 1, 's');
end;
run;
/*put dates into a macro variable pipe delimited*/
proc sql noprint;
select distinct LOOK_AT_MTH into :dates separated by '|'
from work.t0;
quit;
/*macro to loop throgh each date value -- I want the output to be test_21185, test_21216, etc...*/
%macro runloop;
%do i=1 %to %sysfunc(countw(%superq(dates),|));
%let dsname=%sysfunc(compress(%qscan(%superq(dates),&i,|)));
data test_&dsname.;
date=&dsname;
run;
%put &dsname;
%end;
%mend runloop;
%runloop;
@mdavidson Most welcome. I am glad 🙂
The consensus is generally, don't do this.
http://www.sascommunity.org/wiki/Split_Data_into_Subsets
If you insist, there are several approaches outlined in the steps above or in the blog post below. One of the examples in the blog also does dynamic naming.
https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/
Thank you for your input, I agree with you. In general it is best to just use BY group processing. However, in my case I'm taking datasets and running some Enterprise Miner functions on them so I don't have that option available to me. I'm scoring data based on "as of" dates -- so splitting in my case was the obvious choice for me.
Why all that complicated and unnessecary macro code?
data work.t0;
LOOK_AT_MTH="01JAN2018"d;
do while (LOOK_AT_MTH<="01JUN2018"d);
output;
LOOK_AT_MTH=intnx('month', LOOK_AT_MTH, 1, 's');
end;
run;
data _null_;
set work.t0;
call execute('
data test_' !! put(look_at_mth,z5.) !! ';
date=' !! put(look_at_mth,z5.) !! ';
run;
');
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.