topic Macro loop over dates in character format in SAS Programming

Macro loop over dates in character format

ChristianWI — Thu, 12 Aug 2021 14:13:08 GMT

I have a number of datasets of the form:

example_201812

example_201901

...

example 202008

I would like to create a macro where the argument is a starting month of the form eg 201812, and the macro loops over a 12 month period starting with the starting month (eg 201812 to 201911). I have something like this in mind:

%MACRO year(YYYYMM);

%do j = 0 %to 11;
	%let date_&j. = intnx('months',&YYYYMM.,&j.);

  	%if date_&j.= &YYYYMM. %then %do; /*First month*/

 	proc sql;
	create table WANT_&j. as select
	*
	from example_&date_&j.;
	run;

	%end;
	%else %do; /* Remaining months */

 	proc sql;
	create table WANT_&j. as select
	*
	from example_&date_&j.;
	run;

  	%end;

%MEND;

Any help would be greatly appreciated

Re: Macro loop over dates in character format

PaigeMiller — Thu, 12 Aug 2021 14:29:41 GMT

Tip: work with actual SAS date values so that you can use built in SAS functions and built in SAS formats, instead of character strings which you then have to pull apart yourself.

Partial example (UNTESTED)

%macro dothis;
%let start_date=%sysevalf('01DEC2018'd);
%do j=0 %to 11;
     %let thismonth=%sysfunc(intnx(month,&start_date,&j,b);
      proc sql;
                ... whatever ...
                from example_%sysfunc(putn(&thismonth,yymmn6.));
       quit;
%end;
%mend;
%dothis

Now, macro variable &START_DATE is an actual SAS date value (it equals 21519, which is the number of days since 01JAN1960) and then everything works smoothly using SAS date functions (INTNX) and SAS date formats (YYMMN6.)

Re: Macro loop over dates in character format

Reeza — Thu, 12 Aug 2021 15:52:51 GMT

And just a note that if you're trying to append these data sets or combine them somehow, you can use shortcut references.

data combined;
set example_2018:  example_2019: ;
run;

This will append all data sets that start with example_2018 or example_2019.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

Re: Macro loop over dates in character format

ChristianWI — Fri, 13 Aug 2021 01:28:52 GMT

Wow, perfect! Thanks!