topic How to: Use current date to execute macro and pass date parameters in SAS Programming

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

jlhutch — Mon, 25 Oct 2021 17:24:07 GMT

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.

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

svh — Mon, 25 Oct 2021 18:31:08 GMT

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)

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

Reeza — Mon, 25 Oct 2021 19:19:16 GMT

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.

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

jlhutch — Wed, 27 Oct 2021 13:40:07 GMT

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.),"'");