sas macro variable in loop thru end of month-CodePudding

my idea is to get a data set from tables where the variable is dynamically created based on the end of the month date. I know I'm missing sth with format/informat of a variable, but I'm super lost, please help me understood what I'm missing: I need variable YYYYMMDD to look like ie20200229 to be sure to get leap year right. I have database with no partion that's why each end of month is in different table.

%MACRO MEH;
%DO n=2018 %TO 2022;
    &LET yyyy = n;
        %DO i=1 %TO 12;
            %LET mm= i;
            %LET YYYYMMDD= %sysfunc(putn(intnx(month,mdy(( input(&mm.),28,input(&yyyy.)),0,e), YYMMDDN8.));     /*IE. 20200229 */       
RUN;
   /*this part works*/
        PROC SORT DATA=XYZ.NAME_OF_TABLE_&YYYYMMDD.
        out=work.NEW_NAME&YYYYMMDD. nodupkey;
        by SOME_ID;
        RUN;
        /* MORE CODE HERE*/
        DATA work.TEMP2;
        MERGE   work.TEMP1
        work.NEW_NAME&YYYYMMDD;
        BY SOME_ID;
        RUN;
        DATA work.WIN&YYYYMMDD.;
        MERGE   work.TEMP6
                work.TEMP2;
        BY SOME_ID;
        RUN;
        /* MORE CODE Here this code do sorting merging and filtering specific vales i did on SQL but I'm missing some function (like lag, to do in on SQL only) */
    /*END OF WORKING CODE*/

    /*CEAN UP*/
     proc datasets nolist lib=work;
     DELETE NEW_NAME: ;
     DELETE TEMP: ;
     RUN;
%END;
%END;
%MEND MEH;
  %MEH;

CodePudding user response：

To loop over date intervals it is usually easier to use an integer index. You can then use INTNX() function to to calculate the next interval's start date. You can use INTCK() to calculate how many iterations are required.

%let start='01JAN2018'd;
%let end='01DEC2012'd;
%do offset=0 %to %sysfunc(intck(month,&start,&end));
  %let ymd = %sysfunc(intnx(month,&start,&offset),yymmddn8.);
  ....
%end;

CodePudding user response：

Drop the YYYY and mm macro variables as they're redundant
Remove the RUN as well, not required

Then you can replace the top of your macro with the following:

%DO n=2018 %TO 2022;
     %DO i=1 %TO 12;
          %LET YYYYMMDD= %sysfunc(intnx(month, %sysfunc(inputn(&n.&i., yymmn6.)), 0, e), yymmddn8.);     /*IE. 20200229 */       
          %put &yyyymmdd.; * for testing;