SAS macro loop to loop through datasets in Set statement

0
votes

I have 2 SAS macro loops - 1 to create time series dataset and another to append the datasets into a master dataset. Macro Date_loop1 creates time series datasets. In this example I am creating dummy datasets. In reality, the real datasets are several MBs large. So using proc append, within Date_loop1, to the master dataset, results in slow progress. Therefore, I have created a second macro, Date_loop2, to stacks all the datasets above in the SET statement. The issue is that the second loop is not working as intended. Can you please help?

Note: if you run Date_loop1, you can see how the datasets are created. Date_loop2 should also work in the similar manner.

%MACRO LOOP1;
DATA ROLLRATE_&ST_YYYYMM._&ED_YYYYMM.;
ST_YYYYMM = &ST_YYYYMM;
ED_YYYYMM = &ED_YYYYMM;
RUN;
%MEND; 

%MACRO DATE_LOOP1(START,END);

%LET START_YEAR = %SYSFUNC(FLOOR(&START/100));
%LET START_MONTH = %SYSFUNC(MOD(&START,100));
%LET END_YEAR = %SYSFUNC(FLOOR(&END/100));
%LET END_MONTH = %SYSFUNC(MOD(&END,100));

%LET START_DATE = %SYSFUNC(MDY(&START_MONTH,01,&START_YEAR));
%LET END_DATE = %SYSFUNC(MDY(&END_MONTH,01,&END_YEAR));
%LET DIF=%EVAL(%SYSFUNC(INTCK(MONTH,&START_DATE,&END_DATE)));
/*DATA MASTER_ROLLRATE;RUN;DATA ROLLRATE;RUN;*/
%DO I=0 %TO &DIF;
    %DO J=&I %TO &DIF;

    %LET ST_DT = %SYSFUNC(INTNX(MONTH,&START_DATE,&I,B)); /*B - DATE AT THE BEGINNING OF THE WEEK/MONTH/YEAR */
    %LET ST_YEAR = %SYSFUNC(YEAR(&ST_DT));
    %LET ST_MONTH = %SYSFUNC(MONTH(&ST_DT));
    %LET ST_YYYYMM = %EVAL(&ST_YEAR*100+&ST_MONTH);

    %LET ED_DT = %SYSFUNC(INTNX(MONTH,&START_DATE,&J,B));
    %LET ED_YEAR = %SYSFUNC(YEAR(&ED_DT));
    %LET ED_MONTH = %SYSFUNC(MONTH(&ED_DT));
    %LET ED_YYYYMM = %EVAL(&ED_YEAR*100+&ED_MONTH);

    %PUT &ST_YYYYMM &ED_YYYYMM;

    %LET START_YYYYMM = &ST_YYYYMM;%LET END_YYYYMM = &ED_YYYYMM;%LOOP1;RUN;

    %END;
%END;
%MEND DATE_LOOP1;

%DATE_LOOP1(201901,201902);


%MACRO DATE_LOOP2(START,END);

%LET START_YEAR = %SYSFUNC(FLOOR(&START/100));
%LET START_MONTH = %SYSFUNC(MOD(&START,100));
%LET END_YEAR = %SYSFUNC(FLOOR(&END/100));
%LET END_MONTH = %SYSFUNC(MOD(&END,100));

%LET START_DATE = %SYSFUNC(MDY(&START_MONTH,01,&START_YEAR));
%LET END_DATE = %SYSFUNC(MDY(&END_MONTH,01,&END_YEAR));
%LET DIF=%EVAL(%SYSFUNC(INTCK(MONTH,&START_DATE,&END_DATE)));
DATA MASTER_ROLLRATE;
SET
%DO I=0 %TO &DIF;
    %DO J=&I %TO &DIF;

    %LET ST_DT = %SYSFUNC(INTNX(MONTH,&START_DATE,&I,B)); /*B - DATE AT THE BEGINNING OF THE WEEK/MONTH/YEAR */
    %LET ST_YEAR = %SYSFUNC(YEAR(&ST_DT));
    %LET ST_MONTH = %SYSFUNC(MONTH(&ST_DT));
    %LET ST_YYYYMM = %EVAL(&ST_YEAR*100+&ST_MONTH);

    %LET ED_DT = %SYSFUNC(INTNX(MONTH,&START_DATE,&J,B));
    %LET ED_YEAR = %SYSFUNC(YEAR(&ED_DT));
    %LET ED_MONTH = %SYSFUNC(MONTH(&ED_DT));
    %LET ED_YYYYMM = %EVAL(&ED_YEAR*100+&ED_MONTH);
    %LET START_YYYYMM = &ST_YYYYMM;%LET END_YYYYMM = &ED_YYYYMM;

    ROLLRATE_&START_YYYYMM._&END_YYYYMM;

    %END;
%END;
;
RUN;
%MEND DATE_LOOP1;

%DATE_LOOP2(201901,201902);
3
loopsmacrossas
Just a tip for working with dates... you can save yourself a few lines of code if you use the optional second parameter of %sysfunc() to take care of some of the formatting for you. For example, calculating ST_YYYYMM can be done with a single line of code that only requires ST_DT like so: %LET ST_YYYYMM = %SYSFUNC(SUM(&ST_DT),YYMMN6.); This makes use of %sysfunc()s 2nd parameter to format the value being returned into the format you require. I use the sum() function because %sysfunc() requires a function as the first param, you could also use putn() instead of sum(). – Robert Penridge

3 Answers

3
votes

You can use name lists in the SET statement and avoid macro altogether.

Every dataset whose name starts with rollrate will be stacked:

data want;
  set work. rollrate:;
run;
1
votes

There are two issues I am currently seeing

  1. The %mend for second loop is date_loop1 instead of date_loop2 (Second last line of code)

    %MEND DATE_LOOP1; /Should be DATE_LOOP2/

    %DATE_LOOP2(201901,201902);

  2. Please remove semicolon after ROLLRATE_&START_YYYYMM._&END_YYYYMM in date_loop2 macro, because there is a ; after end statements

1
votes

Turn on the MPRINT option to see what code your macro is generating. The second one is generating code like:

MPRINT(DATE_LOOP2):   data master_rollrate;
MPRINT(DATE_LOOP2):   set rollrate_201901_201901;
NOTE: Line generated by the macro variable "ED_YYYYMM".
1         rollrate_201901_201902
          ----------------------
          180
MPRINT(DATE_LOOP2):   rollrate_201901_201902;
NOTE: Line generated by the macro variable "ED_YYYYMM".
1         rollrate_201902_201902
          ----------------------
          180
MPRINT(DATE_LOOP2):   rollrate_201902_201902;
MPRINT(DATE_LOOP2):   ;
MPRINT(DATE_LOOP2):   run;

Remove the extra semi-colon in the middle of the nested %do loops since you are only generating PART of a statement in those loops.

You can simplify your macro code a great deal by using informats and formats to convert your YYYYMM digit strings to and from actual date values.

%macro date_loop2(start,end);
%local start_date end_date dif i j st_yyyymm ed_yyyymm;

%let start_date = %sysfunc(inputn(&start.01,yymmdd8));
%let end_date = %sysfunc(inputn(&end.01,yymmdd8));
%let dif=%sysfunc(intck(month,&start_date,&end_date));

data master_rollrate;
  set

%do i=0 %to &dif;
  %do j=&i %to &dif;
    %let st_yyyymm = %sysfunc(intnx(month,&start_date,&i,b),yymmn6);
    %let ed_yyyymm = %sysfunc(intnx(month,&start_date,&j,b),yymmn6);

  rollrate_&st_yyyymm._&ed_yyyymm

  %end;
%end;
  ;
run;
%mend date_loop2;