Is there a way to use a nested loop in a SAS macro, one for year, one for month, and start and stop mid-year?

Question

I've used nested loops in SAS for years to pull small bits of data at a time, then process/subset the data, then append to a data set at the end of the macro, but I've always had to pull all months (or quarters), then subset my final data set later. What I want to do is to leave off the beginning and end portions of data automagically. In my example below, I want to leave off January of 2017 and December of 2020. The date variable in my data I'm using to subset is stored as a character variable in the format "yyyy-mm-dd". When I run this code, it still shows the months I don't want to see. Does anyone see anything obvious that I'm missing?

%let year1=2017-02; *I'm currently using dates in this format, but the data is too big to pull long periods of data;
%let year2=2020-11;

data _null_;  *taking apart the pieces and getting start/stop year and months assigned as macro vars.;
  year1="&year1";
  year2="&year2";
  y1=substr(year1,1,4);
  y2=substr(year2,1,4);
  m1=substr(year1,6,2);
  m2=substr(year2,6,2);
  call symput('m1',m1);
  call symput('m2',m2);
  call symput('y1',y1);
  call symput('y2',y2);
  put "&y1";
  put "&y2";
  put "&m1";
  put "&m2";
run;

%macro test1234;
%do yr=&y1 %to &y2;  *starting with year;
%do mo=1 %to 12; * nested do loop is for month, and trying to only keep month/year combos within the start/stop dates;
  %if ((yr ne &y1) AND (yr ne &y2)) OR (yr=&y1 and mo ge &m1) OR (yr=&y2 AND mo le &m2) %then %do; 
** for line above: 1st condition: if not a start or stop year, 2nd: start year and not before start month, 3rd, stop year, and not after stop month.;
data _null_; * viewing all the macro variables;
  put "LoopYear" "&yr";
  put "LoopMonth" "&mo";
  put "FirstMonth" "&m1";
  put "FirstYear" "&y1";
  put "LastMonth" "&m2";
  put "LastYear" "&y2";
run;
%end;
%else %do; %end;
%end; %end; 
%mend test1234;
%test1234;

Answer 1

You are missing the ampersands in front of &yr and &mo in your subsetting statement. That is, you should change:

%if ((yr ne &y1) AND (yr ne &y2)) OR (yr=&y1 and mo ge &m1) OR (yr=&y2 AND mo le &m2)

to

%if (&yr ne &y1 and &yr ne &y2) OR (&yr = &y1 and &mo ge &m1) OR (&yr = &y2 and &mo le &m2)

Then you will exclude the first and last month, as you expect.

That being said, the program could undoubtedly be simplified greatly if you used numerical dates instead of character variables.

Answer 2

My recommendation would to refactor your code to use the two parameters and loop by the number of months. Then to change your code to exclude the end points, you can adjust your %DO loop as necessary by changing the intervals.

1. Convert year1 to a SAS date

2. Convert year2 to a SAS date

3. Calculate the number of months between those two dates

4. Loop through the number of months

   %macro loop_dates(year1=, year2=);
   
   *Convert dates to start and end dates in the SAS format;
    %let start_date = %sysfunc(inputn(&year1.-01, yymmdd10.)); /*1*/
    %let end_date = %sysfunc(inputn(&year2.-01, yymmdd10.)); /*2*/
   
    *Calculate the number of months between the two dates;
    %let nMonths = %sysfunc(intck(month, &start_date, &end_date, c)); /*3*/
   
    *check macro variables, not really needed;
    %put %sysfunc(putn(&start_date, date9.));
    %put %sysfunc(putn(&end_date, date9.));
    %put &nMonths;
    *loop over the number of months and create macro variables as needed;
   
    %do i=0 %to &nMonths; /*4*/
        *increment date by loop counter i;
        %let newDate = %sysfunc(intnx(month, &start_date, &i, s));
   
       *calculate your macro variables needed for loop, not sure why???;
        %let yr = %sysfunc(year(&newDate.));
        %let mo = %sysfunc(month(&newDate.));
        %let m1 = %sysfunc(month(&start_date.));
        %let y1 = %sysfunc(year(&start_date.));
        %let m2 = %sysfunc(month(&end_date.));
        %let y2 = %sysfunc(year(&end_date.));
   
        *test macro variables, not needed;
        %put "LoopYear" "&yr";
        %put "LoopMonth" "&mo";
        %put "FirstMonth" "&m1";
        %put "FirstYear" "&y1";
        %put "LastMonth" "&m2";
        %put "LastYear" "&y2";
    %end;
   
    %mend loop_dates;
   
    *Call macro with the date intervals rather than use an un-parameterized macro
    %loop_dates(year1=2017-02, year2=2020-11);
   

This includes the start, 2017-02 and end, 2020-11. To change this to not include the end points, change your %DO by incrementing the start and end. So it now starts at 1 and ends at nMonths - 1.

From:

%do i=0 %to &nMonths;

To:

%do i=1 %to &nMonths-1;