SAS Macro Keeps on Running

[newboy1218]

Hi.. my following macro keeps on running and won't stop.. Can anyone spot any mistake there?

In my macro, I am first checking if the file lib.dataset_&dt2. exists or not. If it does then append the file name to a list. If not, then skip the date dt2, and move on to the next date and next file.

At the end, my macro variable &fileList1 should have all the existing files. For example,

%put &fileList1.;

(lib.dataset_20190501 lib.dataset_20190524 lib.dataset_20190602 lib.dataset_20190603 .....)

rsubmit;
%macro code(d1=, d2=);
%let start_date = &d1.;
%let end_date = &d2.;
%let dt1 = &start_date.;
%do %while (%sysevalf("&dt1."d <= "&end_date."d));
	%let dt1 = %sysfunc(putn("&dt1."d, date9.));
	%let dt2 = &dt1.;
	%let dt2 = %sysfunc(putn("&dt1."d, yymmddn8.));
	%if %sysfunc(exist(lib.dataset_&dt2.)) = 1 %then %do;
		%let fileList1 = %str();
		%let thefile1 = lib.dataset_&dt2.; 
		%let fileList1 = %str(&fileList1 &thefile1); 
		%let dt1 = %sysfunc(intnx(day, "&dt1."d, 1, s), date9.);
	%end;
%end;
%put &fileList1.;

%mend;
endrsubmit;

rsubmit;
%code(d1 = '01MAY2019', d2 = '02JUL2019');
endrsubmit;

[Reeza]

What are you planning to use this for?
If the data doesn't exist you never increment the dates so it never exits.

[newboy1218]

if the dataset does not exist, won't the code skips it and move on to the next date? since I have the if then do statement..

I want to compile all the existing file name onto a macro variable, so later on I can call it in a data-step to append all those files together.

[Reeza]

No, because you have a DO UNTIL loop which doesn't have any default increment. You know that shortcut lists exist then?Many of these work for data set names as well.

https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

So you can have hte first and last date and it will include everything in between:

data want;
set lib.mydata201801:; *all for January 2018;
set lib.myData201801 - lib.myData201812;
run;

[Reeza]

And you can just pull the names from SASHELP.VTABLE instead of manually compiling them as well.

[SASKiwi]

The way your macro is written you should not have quotes on your macro parameters otherwise your dates wont resolve correctly. Also it would be good programming practice to set an upper limit on the number of DO loops so you never end up in this situation.

%code(d1 = '01MAY2019', d2 = '02JUL2019'); * You have this - Dont need quotes;
%code(d1 = 01MAY2019, d2 = 02JUL2019); * Try this;

[ChrisNZ]

Move the DT1 increment after the test block.

[ScottBass]

---

@newboy1218 wrote:

Hi.. my following macro keeps on running and won't stop.. Can anyone spot any mistake there?

In my macro, I am first checking if the file lib.dataset_&dt2. exists or not. If it does then append the file name to a list. If not, then skip the date dt2, and move on to the next date and next file.

At the end, my macro variable &fileList1 should have all the existing files. For example,

%put &fileList1.;

(lib.dataset_20190501 lib.dataset_20190524 lib.dataset_20190602 lib.dataset_20190603 .....)

rsubmit;
%macro code(d1=, d2=);
%let start_date = &d1.;
%let end_date = &d2.;
%let dt1 = &start_date.;
%do %while (%sysevalf("&dt1."d <= "&end_date."d));
	%let dt1 = %sysfunc(putn("&dt1."d, date9.));
	%let dt2 = &dt1.;
	%let dt2 = %sysfunc(putn("&dt1."d, yymmddn8.));
	%if %sysfunc(exist(lib.dataset_&dt2.)) = 1 %then %do;
		%let fileList1 = %str();
		%let thefile1 = lib.dataset_&dt2.; 
		%let fileList1 = %str(&fileList1 &thefile1); 
		%let dt1 = %sysfunc(intnx(day, "&dt1."d, 1, s), date9.);
	%end;
%end;
%put &fileList1.;

%mend;
endrsubmit;

rsubmit;
%code(d1 = '01MAY2019', d2 = '02JUL2019');
endrsubmit;

---

Macros and macro variables are just text.  Yes, we have the %eval and %sysevalf functions, but this line "worries" me.

%do %while (%sysevalf("&dt1."d <= "&end_date."d));

Why make this more complicated than it needs to be?  If it were me, I'd code it this way:

%macro get_dates(start,end);
	data _null_;
		length buffer $32767;  %* adjust as required, but the max length does not really hurt performance ; ;
		do date="&start"d to "&end"d;
			buffer=catx(" ",buffer,put(date,yymmddn8.));
		end;
		call symputx("dates",buffer,"G");
	run;
%mend;
%get_dates(01MAY2019,02JUL2019);
%put &=dates;
%* this is the block of code you need to repeat for each token in the list ;
%macro code;
	%let dataset=lib.dataset_&word;
	%put &=dataset;
%mend;	

%loop(&dates);

* if you want to wrap this in one uber-macro: ;

%macro my_uber_macro(start,end);
	%* create a list of dates ;
	data _null_;
		length buffer $32767;  %* adjust as required, but the max length does not really hurt performance ; ;
		do date="&start"d to "&end"d;
			buffer=catx(" ",buffer,put(date,yymmddn8.));
		end;
		call symputx("dates",buffer,"G");
	run;

	%* run a block of code over those dates ;
	%let i=1;
	%let word=%scan(&dates,&i,%str( ));
	%do %while (&word ne );

		%* this is the bit of code you need to repeat for each token ;
		%let dataset=lib.dataset_&word;
		%put &=dataset;
		%* end of the bit of code you need to repeat ;

		%let i=%eval(&i+1);
		%let word=%scan(&dates,&i,%str( ));
	%end;
%mend;

%my_uber_macro(01MAY2019,02JUL2019);

See https://github.com/scottbass/SAS/blob/master/Macro/loop.sas

for the loop macro.  Save it to your autocall macro library.

If you don't want to use that macro, then just use the concepts in that macro to parse each token in the &dates list, and call a child macro or block of code for each word in the list.  Put the block of code you need to repeat in that child macro.

You'll have to use a different approach if your list of dates won't fit into a macro variable (64K).  Well, actually, the 32K limit of the buffer variable in the data step.  However, I rarely encounter this limit in my day-to-day work.

(if you did encounter that limit, see https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas)

[Tom]

You are making this way too complicated. Date are just INTEGERS, in particular the number of days since 1960. Just use a normal %DO loop.  You will need to use %SYSEVALF() to enable the macro processor to understand how to handle date literals because the implied call to %EVAL() doesn't understand them.

%macro code(d1=, d2=);
%local date dsn;
%if not %symexist(filelist1) %then %global filelist1;
%do date=%sysevalf(&d1) %to %sysevalf(&d2) ;
  %let dsn=lib.dataset_%sysfunc(putn(&date,yymmddn8));
  %if %sysfunc(exist(&dsn)) %then %let filelist1=&filelist1 &dsn;
%end;
%mend;

Then call it with actual date values, not strings. Either data literals

%code(d1 = '01MAY2019'd, d2 = '02JUL2019'd);
%put &=filelist1;

or just the raw number of days.

%code(d1=21670,d2=21732);
%put &=filelist1;

[Kurt_Bremser]

Are you working on a submission to the Obfuscated SAS Code Contest? With this, you might get an honourable mention.

You are using lots of data step functions, so you should let them loose in their natural habitat, which is the data step, and only wrap the macro definition around it for easy reuse:

%macro make_filelist(lib=,prefix=,d1=,d2=);
data _null_;
length filelist $32767;
do dt2 = &d1. to &d2.;
  dsname = "&lib..&prefix." !! put(dt2,yymmddn8.);
  if exist(dsname)
  then filelist = catx(' ',filelist,dsname);
end;
call symputx('filelist',filelist,'g');
run;
%mend;
%make_filelist(lib=lib,prefix=dataset_,d1='01may2019'd,d2='02jul2019'd)

You can, as already mentioned, use raw date values when calling the macro.

Always strive for the most simple and straightforward solution. Which usually means macro code is not needed.

[Kurt_Bremser]

PS and if you need to repeat a certain action for those datasets, change the data step:

data control;
do dt2 = &d1. to &d2.;
  dsname = "&lib..&prefix." !! put(dt2,yymmddn8.);
  if exist(dsname) then output;
end;
keep dsname;
run;

Now you have a dataset from which you can dynamically run this action with call execute. And you can't run into any limits for character or macro variables.