SAS MACRO to Cycle Month End Dates

Question

I have a set of code that I am manually adjusting month end dates. The query which runs and uses the dates has left joins to pull data for each declared date. I've been looking at macros because this is labor intensive to change all the dates by one month all the way until the most recent completed month. There's got to be a way to loop through and change each declared month end date by one month and do a loop to run the query until it gets to the most recent completed month end date.

So after the below runs entirely, it would up each declared date by one month and show the month end date. Each data set that runs would be be BEDT. So the first run would be jan2018, so on and so forth.

Can someone assist or point me into the right direction? I've read through so much documentation and have gotten no where. Thanks in advance.

%Let BEdt='01JAN2018'd

%let dt1='28FEB2018'
%let dt2='31MAR2018'
%let dt3='30APR2018'
%let dt4='31MAY2018'
%let dt5='30JUN2018'
%let dt6='31JUL2018'
%let dt7='31AUG2018'
%let dt8='30SEP2018'
%let dt9='31OCT2018'
%let dt10='30NOV2018'
%let dt12='31DEC2018'
%let dt12='31JAN2019'

Answer 1

Let's use something that has a start date and a number of months to pull. We'll use intnx() to increment each month one by one until we reach the last month. intnx() will also help us calculate the start and end dates.

%macro get_data(start=, months=12);

    /* Convert start/end values into SAS dates */
    %let start_dt = %sysfunc(inputn(&start., date9.));

    %do i = 0 %to &months.;

        /* Calculate the start and end months for the month of data in the loop */
        %let month_s = %sysfunc(intnx(month, &start_dt., &i., B) );
        %let month_e = %sysfunc(intnx(month, &start_dt., &i., E) );

        %put Pulling data for %cmpres(%sysfunc(putn(&month_s., date9.)) - %sysfunc(putn(&month_e., date9.)));

        /***********************************/
        /***** SQL/DATA Step goes here *****/
        /***********************************/

    %end;
%mend;

%get_data(start=01JAN2018);

In the area where you'd add SQL or DATA step code, it could look like this:

data want_&month_s.;
    set have;
    where date BETWEEN &month_s. AND &month_e.;
run;

Output:

Pulling data for 01JAN2018 - 31JAN2018
Pulling data for 01FEB2018 - 28FEB2018
Pulling data for 01MAR2018 - 31MAR2018
Pulling data for 01APR2018 - 30APR2018
Pulling data for 01MAY2018 - 31MAY2018
Pulling data for 01JUN2018 - 30JUN2018
Pulling data for 01JUL2018 - 31JUL2018
Pulling data for 01AUG2018 - 31AUG2018
Pulling data for 01SEP2018 - 30SEP2018
Pulling data for 01OCT2018 - 31OCT2018
Pulling data for 01NOV2018 - 30NOV2018
Pulling data for 01DEC2018 - 31DEC2018
Pulling data for 01JAN2019 - 31JAN2019

Answer 2

Depending on the complexity of the statement you want to run for each month, you can also use call execute instead of a macro loop.

data _null_;
  start_date = "&start."d;
  do i = 0 to &months.;
    month_start = intnx("month", start_date, i, "B");
    month_end = intnx("month", start_date, i, "E");
    call execute(
      'data want_'||strip(i)||';'||
        'set have;'||
        'where date between '||month_start||' and '||month_end||';'||
       'run;'
    );
  end;
run;

You only have to be careful about when macro variables get resolved. I nice article on this can be found here

Answer 3

* Do whatever you would do for one month in a macro;
%macro do_a_month(begin_d, end_d);
    data want_&begin_d;
        set have;
        where data between "&begin_d"d and "&end_d"d;
    run;
%mend;

* call the macro as much as needed;
data _null_;
    begin_d = '01JAN2018'd;
    do while (begin_d le '31JAN2019'd);
        end_d = intnx("month", begin_d, 0, "E");
        call execute ( '&do_a_month('
            || put(begin_d, date9.) ||','
            || put(end_d, date9.) ||')';
        * go to next month;
        begin_d = end_d + 1;
    end;
run;