Loop monthly tables and stack them in data step

[fwu811]

Hi SAS Community users,

I am trying to loop a large number of historical data tables, and stack them into a single table using datastep.

For example, these tables named as comp_mmmyy, e.g., comp_jan24, comp_feb24,..., comp_jun24, and I am using the following code in EG:

%macro test;
data tmp;
set %do lp = &lp_min. %to &lp_max.; comp_&put(intnx("month","31dec1990"d,&lp.,"e"),monyy5.) %end;; run;
%mend;

%test;

I used to put function to convert a date format into mmmyy but got a warning for it, followed by errors in intnx().

Can you please let me where I am wrong?

Thank you in advance.

[Tom · Accepted solution]

To have the macro processor run a SAS function you need to nest the call in %SYSFUNC() macro function.  And you have to call PUTN() (or PUTC for character values) rather then PUT().  I assume because SAS found it was too hard to get %SYSFUNC() to work with PUT() and since the functionality is available with the other two function why bother.

But you do not need to use the PUTN() function since you can pass the format to be applied to the result as the optional second argument to %SYSFUNC().

Also there is no interval named "MONTH".  Do not add the quotes into the values of the strings you use in macro code.  Everything is a string to the macro processor.

%sysfunc(intnx(month,"31dec1990"d,&lp.,e),monyy5.) 

You might want to either assign the dataset name to a macro variable first or wrap the name in a %UNQUOTE() function call to make sure that the macro code does not confuse the parser into thinking you wanted two datasets named COMP_ and JAN24.

%macro test;
%local lp dsname ;
data tmp;
  set 
%do lp = &lp_min. %to &lp_max.; 
  %let dsname=comp_%sysfunc(intnx(month,"31dec1990"d,&lp.,e),monyy5.) ;
  &dsname
%end;
  ; 
run;
%mend;

%test; 

Note that it would work much better if the datasets where named COMP_202401 to COMP_202406.  Not only would the dataset names sort in chronological order but then you would not need any (or at least not as much) macro code:

data tmp;
   comp_202401 - comp_202406 ;
run;

[fwu811]

Thanks, Tom.
the scripts works with a %sysfunc() and the removal of quotes.

[ballardw]

If you want ALL the data sets combined and do not have any others in the library whose names start with COMP_ this if very easy:

data tmp;
   set comp_: ;
run;

The : immediately following part of the dataset name tells SAS to build a list of all names that start with the characters COMP_

I would be tempted to add the INDSNAME option to capture the name of the data set that each record comes from:

data tmp;
   set comp_:  indsname=dsn ;
   length dataset $ 41 ;
   dataset=dsn;
run;

Note: If you use different naming practices such as COMP_YYYYMM where YYYY is a 4-digit year and MM is a 2-digit month with leading 0 as needed you could use such lists like

Comp_2021: to get all the sets in year 2021

Comp_202005 - Comp_202009 to get May through Sep .

Also note that the typical file display programs would display these in chronological order.

[fwu811]

Hi,

I need to define the mmmyy range to consolidate the historical data, so the min and max of the loop need to be specified. 

[Kurt_Bremser]

From more than two decades of experience in this job, and four decades in IT:

NEVER use 2-digit years.

NEVER use month names in filenames; use month numbers.

ALWAYS use a YMD order for dates; this sorts chronologically on its own, and makes the use of wildcards much easier.