Dynamic dataset name from macro loop

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

Dynamic dataset name from macro loop

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;

Accepted Solutions
Solution
‎06-18-2018 03:25 PM
PROC Star
Posts: 1,817

Re: Dynamic dataset name from macro loop

Posted in reply to mdavidson
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;

View solution in original post


All Replies
Super User
Posts: 6,778

Re: Dynamic dataset name from macro loop

[ Edited ]
Posted in reply to mdavidson

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.

Solution
‎06-18-2018 03:25 PM
PROC Star
Posts: 1,817

Re: Dynamic dataset name from macro loop

Posted in reply to mdavidson
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;
Contributor
Posts: 61

Re: Dynamic dataset name from macro loop

Posted in reply to novinosrin
This is excellent and worked great for me, thank you for taking the time to come up with some great code!
PROC Star
Posts: 1,817

Re: Dynamic dataset name from macro loop

Posted in reply to mdavidson

@mdavidson Most welcome. I am glad Smiley Happy

Super User
Posts: 23,740

Re: Dynamic dataset name from macro loop

Posted in reply to mdavidson

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/

 

Contributor
Posts: 61

Re: Dynamic dataset name from macro loop

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. 

Super User
Posts: 10,258

Re: Dynamic dataset name from macro loop

Posted in reply to mdavidson

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 213

Re: Dynamic dataset name from macro loop

Posted in reply to mdavidson
"Why all that complicated and unnessecary macro code?" - Maybe, because call execute statements are hardly readable and thus difficult to debug. But since @Reeza posted the important hint: don't split data, it hardly matters how crappy the code is.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 221 views
  • 3 likes
  • 6 in conversation