topic Re: Dynamic dataset name from macro loop in SAS Programming

Dynamic dataset name from macro loop

mdavidson — Thu, 14 Jun 2018 20:41:00 GMT

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;

Re: Dynamic dataset name from macro loop

Astounding — Thu, 14 Jun 2018 21:10:57 GMT

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.

Re: Dynamic dataset name from macro loop

novinosrin — Thu, 14 Jun 2018 21:08:38 GMT

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;

Re: Dynamic dataset name from macro loop

Reeza — Fri, 15 Jun 2018 01:02:58 GMT

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/

Re: Dynamic dataset name from macro loop

Kurt_Bremser — Fri, 15 Jun 2018 09:15:47 GMT

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;

Re: Dynamic dataset name from macro loop

error_prone — Sun, 17 Jun 2018 07:59:14 GMT

"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.

Re: Dynamic dataset name from macro loop

mdavidson — Mon, 18 Jun 2018 19:26:29 GMT

This is excellent and worked great for me, thank you for taking the time to come up with some great code!

Re: Dynamic dataset name from macro loop

mdavidson — Mon, 18 Jun 2018 19:29:31 GMT

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.

Re: Dynamic dataset name from macro loop

novinosrin — Mon, 18 Jun 2018 19:48:47 GMT

@mdavidson Most welcome. I am glad 🙂