topic Re: Macro loop to append dataset with MONYY in the dataset names in SAS Programming

Macro loop to append dataset with MONYY in the dataset names

boomboomtanner — Wed, 31 Aug 2022 00:09:01 GMT

Hi team,

I am looking to create one master table which contains 13 months worth of data.

Currently the table names are as such:

XXX_AUG22

XXX_JUL22

XXX_JUN22

... etc

Is there a way to append these tables using a macro loop by combining this month's data + the last 12 months?

Appreciate your help!

Re: Macro loop to append dataset with MONYY in the dataset names

ghosh — Wed, 31 Aug 2022 15:39:12 GMT

data lstmonth;
  do i=0 to 13;
    dates=cats('XXX_',put(intnx('month',today(),-i,'s'),monyy.));
    output;
  end;
run;

proc sql noprint;
    select dates into :datlst separated by ' '
    from lstmonth ;
quit;

%put &=datlst;

/*  you will get this list: 
DATLST=
XXX_AUG22 XXX_JUL22 XXX_JUN22 XXX_MAY22 XXX_APR22 XXX_MAR22 XXX_FEB22 XXX_JAN22 XXX_DEC21 XXX_NOV21 XXX_OCT21 XXX_SEP21 XXX_AUG21 XXX_JUL21 
*/

data combined;
set &datlst 
  indsname = source; /* the INDSNAME= option stores the dataset name */
run;

Re: Macro loop to append dataset with MONYY in the dataset names

Kurt_Bremser — Wed, 31 Aug 2022 05:32:09 GMT

As a very big hint for the future: never use date strings like these in dataset names or similar. Use a YYYYMM string instead.

You can then combine a year simply like this:

data want;
set have_2022:;
run;

In all displays, the datasets will always sort in chronological order.