SAS MACRO, DO LOOP, YYYYMM INCREMENTS

[steveocaffey]

Hello,

I am in need of some coding assistance after messing around with it for too long.  I have a very simple macro with 3 date parameters that I want to increment by one month in a do loop almost 95 times.  So instead of writing ~100 lines of code for each date, the do loop would simply increment the date by one month.  My date variable is formatted as YYYYMM.  The macro is pulling 30 months of data for each iteration.

Thank you for any assistance.

Steve

/* INDIVIDUAL MACROS */

%MONTH_LOOP(LAG6=201207,START_MONTH=201301,END_MONTH=201412);
%MONTH_LOOP(LAG6=201208,START_MONTH=201302,END_MONTH=201501);
%MONTH_LOOP(LAG6=201209,START_MONTH=201303,END_MONTH=201502);
...
%MONTH_LOOP(LAG6=202104,START_MONTH=202110,END_MONTH=202309);

/*TESTING DO LOOP */

%MACRO LOOPIT(START_MONTH1=,START_MONTH2=,STOP_MONTH=);

%LET SETSTR=;

    %LOCAL SEQUENCE;

    %DO SEQUENCE=&START_MONTH2 %TO &STOP_MONTH;

        %PUT SEQUENCE: &SEQUENCE;

%GETDATA(LAG6=&START_MONTH1,START_MONTH=&START_MONTH2,END_MONTH=&STOP_MONTH);

%LET SETSTR=&SETSTR STEVEO.SUMM_&MONTH.; 

        %IF %SUBSTR(&SEQUENCE, 5, 2) = 12 %THEN %LET SEQUENCE = %EVAL(&SEQUENCE + 88);

    %END;

%PUT FINAL SET STATEMENT=&SETSTR;

%MEND LOOPIT;

%LOOPIT(START_MONTH1=201207,START_MONTH2=201301,STOP_MONTH=201412);

[PaigeMiller]

Work with macro variables that are valid SAS date values, which is the number of days since 01JAN1960. The macro variables you show are not valid SAS date values, 201207 is not a valid SAS date value, even if it looks like a valid date value to humans. Work with functions like INTNX and INTCK, which allow the counting and incrementing of months — SAS has already done the hard work to handle months properly when you have valid SAS dates, so you don't have to.

Here is a simplified version of such a macro that will loop through all months specified. It works with macro variables which are date literals (&start_month and &end_month) or in the case of &thismonth, it is a valid SAS date value.

%macro increment_months(start_month=,end_month=);
    %let thismonth=%sysevalf(&start_month);
    %do %while(&thismonth <= %sysevalf(&end_month));
        /* Some macro code goes here using &thismonth which is a valid SAS date value */
        %put %sysfunc(putn(&thismonth,yymmn6.));
        %let thismonth=%sysfunc(intnx(month,&thismonth,1,b));  /* Increment month by 1 */
    %end;
%mend;
%increment_months(start_month='01JAN2013'd,end_month='01DEC2014'd)

I'm sure that with the above framework, you can adapt it to your actual problem.

[Tom]

When you need to work with date or time intervals that cannot be expressed as multiples of the unit used to store the values (days for DATE and seconds for TIME and DATETIME values) then you should use INTNX() function.

You can use INTCK() to determine how many intervals there are.

In normal code that might look like:

do offset=0 to intck('month',start,end);
  date=intnx('month',start,offset);
  ...
end;

In macro code that will look like:

%do offset=0 %to %sysfunc(intck(month,&start,&end));
  %let date=%sysfunc(intnx(month,&start,&offset);
  ...
%end;

If you need to convert the date into those 6 digit strings you are using you could use a format.

%let yyyymm = %sysfunc(putn(&date,yymmn6.));

If you need to convert one of those 6 digit strings you are passing to the macro into a date use an informat.

%let yyyymm=201207;
%let date=%sysfunc(inputn(&yyyymm.01,yymmdd8.));

[PaigeMiller]

Hello, @steveocaffey , you sent me some questions in e-mail. All questions must be asked here in the public forums, so everyone can benefit from the discussion and answers.

[steveocaffey]

Thank you, was trying to reduce the back and forth details on my task.  So, after spending a good chunk of today trying different tweaks, I am simply struggling getting the dates to increment by a month.

The current problem that I have is getting the dates to increment by a month after the last end statement (highlighted in red font).  I tried adding the MDY function with SUBSRT or SUBSTRN, but that does not seem to work resulting in a missing value when trying to increment THISMONTH1 and THISMONTH2 by a month.

Sincerely appreciate the help.

*********************************************************************************************************************************************

%LET EOP=202309;

%MACRO INCREMENT_MONTHS(INCRMT1_LAG6=,END_MONTH=,INCRMT2_TZERO=);

    %LET THISMONTH1=%SYSEVALF(&INCRMT1_LAG6);  /* LAG 6 */

    %LET THISMONTH2=%SYSEVALF(&INCRMT2_TZERO);  /* TIME ZERO */

%LET THISMONTH3=%SYSEVALF(&END_MONTH);  /* LAST MONTH OF PERFORMANCE WINDOW */

    %DO %WHILE(&THISMONTH2 <= %SYSEVALF(&THISMONTH3));  /* DO LOOP IS TIME ZERO TO END OF PERFORMANCE  */

        %PUT %SYSFUNC(PUTN(&THISMONTH1,YYMMN6.));

%PUT %SYSFUNC(PUTN(&THISMONTH2,YYMMN6.));

%PUT %SYSFUNC(PUTN(&THISMONTH3,YYMMN6.));

    /* SOME MACRO CODE GOES HERE USING &THISMONTH WHICH IS A VALID SAS DATE VALUE */

%PULLIT(BY=,WHERE=,CLASS=&SPLITTER,TIMEZERO=&THISMONTH2,START_MONTH=&THISMONTH1,STOP_MONTH=&EOP);

      %LET THISMONTH1=%SYSFUNC(INTNX(MONTH,MDY(SUBSTR(&THISMONTH1,5,2), 1,SUBSTR(&THISMONTH1,1,4)),1),YYMMN6.);  /* INCREMENT MONTH BY 1 */

  %LET THISMONTH2=%SYSFUNC(INTNX(MONTH,MDY(SUBSTR(&THISMONTH2,5,2), 1,SUBSTR(&THISMONTH2,1,4)),1),YYMMN6.);  /* INCREMENT MONTH BY 1 */

  %END;

%MEND;

%INCREMENT_MONTHS(INCRMT1_LAG6=202109,END_MONTH=202205,INCRMT2_TZERO=202204);

[PaigeMiller]

I have not tried to debug all of your macro, but if you are going to use this

 %LET THISMONTH1=%SYSEVALF(&INCRMT1_LAG6);

then you MUST use this:

%INCREMENT_MONTHS(INCRMT1_LAG6='01SEP2021'd,END_MONTH='01MAY2022'd,INCRMT2_TZERO='01APR2022'd)

Alternatively, you could use

%INCREMENT_MONTHS(INCRMT1_LAG6=202109,END_MONTH=202205,INCRMT2_TZERO=202204)

with

%let thismonth1 = %sysfunc(inputn(&incrmt1_lag6,yymm6.));

[Tom · Accepted solution]

You appear to want to iterate a over the number of months.  It is much easier to iterate N times using a normal DO loop instead of a DO WHILE loop.   You don't need to write complicated incrementation logic or complex WHILE or UNTIL conditional test.  SAS will do the counting and the incrementing for you.

I cannot tell what you are using the third "month" input for, but here is an example of how to loop from TZERO to END_MONTH.

%macro increment_months(incrmt1_lag6=,end_month=,incrmt2_tzero=);
%local sdate edate offset thismonth;
%let sdate=%sysfunc(inputn(&incrmt2_tzero.01,yymmdd8.));
%let edate=%sysfunc(inputn(&end_month.01,yymmdd8.));

%do offset=0 %to %sysfunc(intck(month,&sdate,&edate));
  %let thismonth=%sysfunc(intnx(month,&sdate,&offset));
  %put &=offset &=thismonth %sysfunc(putn(&thismonth,date9.)) %sysfunc(putn(&thismonth,yymmn6.));
%end;
%mend;

%increment_months(incrmt1_lag6=202109,end_month=202205,incrmt2_tzero=202204);

Result

76   %increment_months(incrmt1_lag6=202109,end_month=202205,incrmt2_tzero=202204);
OFFSET=0 THISMONTH=22736 01APR2022 202204
OFFSET=1 THISMONTH=22766 01MAY2022 202205

[steveocaffey]

Thanks Tom, it worked great!  This is a very nice solution for my task.  I made some slight modifications below.   This will save writing 100+ lines of code.

%macro increment_months(incrmt1_lag6=,end_month=,incrmt2_tzero=);

%local sdate edate offset thismonth

   ldate thismonth1

;

%let sdate=%sysfunc(inputn(&incrmt2_tzero.01,yymmdd8.));

%let edate=%sysfunc(inputn(&end_month.01,yymmdd8.));

%let ldate=%sysfunc(inputn(&incrmt1_lag6.01,yymmdd8.));  

%do offset=0 %to %sysfunc(intck(month,&sdate,&edate));

  %let thismonth=%sysfunc(intnx(month,&sdate,&offset));

  %let thismonth1=%sysfunc(intnx(month,&ldate,&offset));

  %put &=offset &=thismonth %sysfunc(putn(&thismonth,date9.)) %sysfunc(putn(&thismonth,yymmn6.));

  %put &=offset &=thismonth1 %sysfunc(putn(&thismonth1,date9.)) %sysfunc(putn(&thismonth1,yymmn6.));

  %PULLIT(BY=,WHERE=,CLASS=&SPLITTER,

  TIMEZERO=%sysfunc(putn(&thismonth,yymmn6.)),

  START_MONTH=%sysfunc(putn(&thismonth1,yymmn6.)),

  STOP_MONTH=&EOP);

%end;

%mend increment_months;

%increment_months(incrmt1_lag6=202109,end_month=202205,incrmt2_tzero=202204);

[Patrick]

If you just need to call the same macro multiple times with different parameter values then using a SAS datastep with call execute() is another option. 

%macro month_loop(lag6=,START_MONTH=,END_MONTH=);
  data _null_;
    put "&lag6 &start_month &end_month";
  run;
%mend;


%let start_yymm=201301;
%let stop_yymm =201412;

data _null_;
  start_dt=input("&start_yymm",yymmn6.);
  end_dt  =input("&stop_yymm",yymmn6.);

  do i=0 by 1;
    param_dt=intnx('month',start_dt,i,'b');
    if param_dt>end_dt then leave;

    length cmd $1000;
    cmd=cats('%MONTH_LOOP(LAG6=', put(param_dt,yymmn6.));

    param_dt=intnx('month',start_dt,i+6,'b');
    cmd=cats(cmd, ',START_MONTH=', put(param_dt,yymmn6.));

    param_dt=intnx('month',start_dt,i+29,'b');
    cmd=cats(cmd, ',END_MONTH=', put(param_dt,yymmn6.),');');
    /* put cmd; */
    call execute(cmd);
  end;
run;