BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mdavidson
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

8 REPLIES 8
Astounding
PROC Star

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.

novinosrin
Tourmaline | Level 20
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
Quartz | Level 8
This is excellent and worked great for me, thank you for taking the time to come up with some great code!
novinosrin
Tourmaline | Level 20

@mdavidson Most welcome. I am glad 🙂

Reeza
Super User

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/

 

mdavidson
Quartz | Level 8

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. 

Kurt_Bremser
Super User

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;
error_prone
Barite | Level 11
"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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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