How to: Use current date to execute macro and pass date parameters

[jlhutch]

I'm very new to SAS would like to use use the current date to determine whether on not to execute a macro that passes date parameters to another macro. I've been editing each month to comment out the part I don't need to execute, but I'm sure there has to be a more elegant way. This is basically what I have now:

%macro pull_accounts(date, datelabel);

/* date in yyyy-mm-dd date label in ddMMMyyyy */

/* Create table of latest events per account */

%let asof = Date(&date.);

%let asoflab = &datelabel.;

%let str1  = %STR(

with table1 as 

(Select a.field1, a.field2, a.field3, a.field4, b.field1, c.field1, d.field1, d.field2, d.field3, e.field1,

row_number() over (partition by a.field2 order by a.field3, d.field2, a.field1) as row,

row_number() over (partition by a.field2 order by a.field3) as row_earliest

from a

left join b on a.field1 = b.field2

left join c on a.field1 = c.field2

left join d on a.field1 = d.field4

left join e on d.field5 = e.field2

where (a.field2 <= d.field5 and d.field1 <= &asof.)

and (a.field3 is null or a.field3>= &asof.)),

/*followed by several more intermediate tables then a final results table*/

);

PROC SQL FEEDBACK;

    CONNECT TO REDSHIFT (&MyLibe DSN="My-Reports" AUTHDOMAIN="My-Reports-Auth");

CREATE TABLE work.last_event_&asoflab. AS

SELECT * FROM CONNECTION TO REDSHIFT (%QSYSFUNC(DEQUOTE(&str1)));

QUIT;

%mend pull_accounts;

%pull_accounts('2021-06-01', 01JUN2021);

%pull_accounts('2021-07-01', 01JUL2021);

%pull_accounts('2021-08-01', 01AUG2021);

%pull_accounts('2021-09-30', 30SEP2021);

/*%pull_accounts('2021-10-31', 31OCT2021);

%pull_accounts('2021-11-30', 30NOV2021);

%pull_accounts('2021-12-31', 31DEC2021);

%pull_accounts('2022-01-31', 31JAN2022);

%pull_accounts('2022-02-28', 28FEB2022);

%pull_accounts('2022-03-31', 31MAR2022);

%pull_accounts('2022-04-30', 30APR2022);

%pull_accounts('2022-05-31', 31MAY2022);*/

%hive_drop_table(my_datasets,last_per_month);

data lib_hive.last_per_month;

month = .;

year = .;

  set work.last_event_01JUN2021

work.last_event_01JUL2021

work.last_event_01AUG2021

work.last_event_30SEP2021

/*work.last_event_31OCT2021

work.last_event_30NOV2021

work.last_event_31DEC2021

work.last_event_31JAN2022

work.last_event_28FEB2022

work.last_event_31MAR2022

work.last_event_30APR2022 

work.last_event_31MAY2022 */;

month = month(asof);

year = year(asof);

run;

Each month I shift to comment out the next section, but what I would like to be do is check the current date and only execute the macros where the dates being passed are after today's date.

[svh]

I remember there being a similar example in this book. The author showed how to use macro variables do automate a report by date based on a condition. 

Chapter 5 (sas.com)

[Reeza · Accepted solution]

Refactor your code to use a data set not manual calls and then you can easily filter it based on a date.

Note that I accounted for the change in beginning of the month intervals to end of month intervals with the INTERVAL variable. E means end of the month, B means beginning of the month. Uncomment the CALL EXECUTE to see the macro code actually run. You can change that step to a data _null_ step as well once you've confirmed it's working.

data date_calls;
date_start = '01Jun2021'd;
interval = 'b';
do i=1 to 12;
if month(_date) >=8 then interval = 'e';
_date = intnx('month', date_start, i-1, interval);

date = quote(put(_date, yymmddd10.));
date_label = put(_date, date9.);
output;
end;
run;

data macro_execution;
set date_calls;
where _date <= today();

str = catt('%pull_accounts(', 
             date,
             ' , ',
             date_label,
             ');');
*call execute(str);
run;

---

@jlhutch wrote:

I'm very new to SAS would like to use use the current date to determine whether on not to execute a macro that passes date parameters to another macro. I've been editing each month to comment out the part I don't need to execute, but I'm sure there has to be a more elegant way. This is basically what I have now:

%macro pull_accounts(date, datelabel);

/* date in yyyy-mm-dd date label in ddMMMyyyy */

/* Create table of latest events per account */

%let asof = Date(&date.);

%let asoflab = &datelabel.;

%let str1  = %STR(

with table1 as 

(Select a.field1, a.field2, a.field3, a.field4, b.field1, c.field1, d.field1, d.field2, d.field3, e.field1,

row_number() over (partition by a.field2 order by a.field3, d.field2, a.field1) as row,

row_number() over (partition by a.field2 order by a.field3) as row_earliest

from a

left join b on a.field1 = b.field2

left join c on a.field1 = c.field2

left join d on a.field1 = d.field4

left join e on d.field5 = e.field2

where (a.field2 <= d.field5 and d.field1 <= &asof.)

and (a.field3 is null or a.field3>= &asof.)),

/*followed by several more intermediate tables then a final results table*/

);

PROC SQL FEEDBACK;

    CONNECT TO REDSHIFT (&MyLibe DSN="My-Reports" AUTHDOMAIN="My-Reports-Auth");

CREATE TABLE work.last_event_&asoflab. AS

SELECT * FROM CONNECTION TO REDSHIFT (%QSYSFUNC(DEQUOTE(&str1)));

QUIT;

%mend pull_accounts;

%pull_accounts('2021-06-01', 01JUN2021);

%pull_accounts('2021-07-01', 01JUL2021);

%pull_accounts('2021-08-01', 01AUG2021);

%pull_accounts('2021-09-30', 30SEP2021);

/*%pull_accounts('2021-10-31', 31OCT2021);

%pull_accounts('2021-11-30', 30NOV2021);

%pull_accounts('2021-12-31', 31DEC2021);

%pull_accounts('2022-01-31', 31JAN2022);

%pull_accounts('2022-02-28', 28FEB2022);

%pull_accounts('2022-03-31', 31MAR2022);

%pull_accounts('2022-04-30', 30APR2022);

%pull_accounts('2022-05-31', 31MAY2022);*/

%hive_drop_table(my_datasets,last_per_month);

data lib_hive.last_per_month;

month = .;

year = .;

  set work.last_event_01JUN2021

work.last_event_01JUL2021

work.last_event_01AUG2021

work.last_event_30SEP2021

/*work.last_event_31OCT2021

work.last_event_30NOV2021

work.last_event_31DEC2021

work.last_event_31JAN2022

work.last_event_28FEB2022

work.last_event_31MAR2022

work.last_event_30APR2022 

work.last_event_31MAY2022 */;

month = month(asof);

year = year(asof);

run;

Each month I shift to comment out the next section, but what I would like to be do is check the current date and only execute the macros where the dates being passed are after today's date.

---

[jlhutch]

Thanks! I only had to make one tiny adjustment because I need the output for date to be single quoted.
I changed: date = quote(put(_date, yymmddd10.));
to: date = quote(put(_date, yymmddd10.),"'");