SAS loop through month-ends in range

Question

Suppose that I have a library save which contains daily files thefile_dly_yyyymmdd,

e.g. save.thefile_dly_20150831, save.thefile_dly_20150901, ... , save.thefile_dly_20210731.

I want to perform some manipulation on the historical data in this library, however, I only want to extract the files between a specific date, and also only want to keep the file which corresponds to the last file for each month, e.g. I want to extract save.thefile_dly_20150831, save.thefile_dly_20150930, save.thefile_dly_20151031, etc.

Something like the following.

%macro loop_through(start,end);
 %do i = &start. %to &end.;
    %if %sysunc(exist(SAVE.THEFILE_DLY_&i.)) %then %do;
      /* Do some data processing on the file */
    %end;
 %end;
%mend;

%loop_though(20150831,20210731);

The problem is that the abovementioned code will loop through every single integer between 20150831 and 20210731, which is not optimal, and also, it will process every single file that exists for the month, and not just the file corresponding to the last day of each month.

How can I adjust? Any advice will be appreciated.

Tom Tom · Accepted Answer · 2021-09-14T13:36:33

To loop over calendar intervals iterate over the number of intervals. Use the INTNX() function to calculate the next interval's date. Use the INTCK() function to calculate the number of intervals requested.

%macro loop_through(start,end);
  %local offset ymd dsname ;
  %do offset = 0 %to %sysfunc(intck(month,&start,&end));
     %let ymd=%sysfunc(intnx(month,&start,&offset,end),yymmddn8.);
     %let dsname=SAVE.THEFILE_DLY_&ymd;
     %if %sysunc(exist(&dsname)) %then %do;
       /* Do some data processing on the file */
     %end;
  %end;
%mend;

%loop_though('01AUG2015'd,'01JUL2021'd);

If you really want to allow the user of the macro to pass in YYYYMMDD digit strings instead of actual SAS date values then add some logic to your macro to convert the digit strings into actual date values. For example:

%let start=%sysfunc(inputn(&start,yymmdd8.));

SAS loop through month-ends in range

1 Answers