macro function to output yesterday's data and 3 days of data if the weekend

[Roddy]

I am using the following macro variable to give me yesterday's data ... %let startdate = today()-1

How do I set this up so that this works during the week, but when I pull the data on Monday, the results include Friday, Saturday and Sunday?

Example:

proc sql;

create table order as select id, createdate

from x.table

where createdate = &startdate.

; quit;

[FreelanceReinh]

You could define macro variable STARTDATE as follows:

data _null_;
if weekday(today())=2 then call symput('startdate', 'today()-1 | createdate=today()-2 | createdate=today()-3');
else call symput('startdate', 'today()-1');
run;

Alternatively, if it was no problem to change your WHERE condition to

where today()-createdate in (&daylist)

you could define DAYLIST like this:

data _null_;
if weekday(today())=2 then call symput('daylist', '1,2,3');
else call symput('daylist', '1');
run;

Edit: Please note that in both cases the code to create the macro variable would need to run on the same day as the PROC SQL step. 

[dcruik]

I don't think your %let statement is correct, as SAS would read the value of startdate as "today()-1".  The below macro will create a startdate and an enddate for the createdate filter.  If the current day you run it is Monday, it will set startdate 3 days ago and enddate to yesterday.  If the current day you run it is not a Monday, it will set both startdate and enddate to yesterday.  I also modify your where statement in the SQL procedure to call the dates with a date literal.  Hope this helps!

%macro createdate;
%let today=%sysfunc(today(),date9.);
%let day=%sysfunc(weekday("&today"d));

%if &day=2 %then %do;
	%let startdate=%sysfunc(intnx(DAY,"&today"d,-3),date9.);
	%let enddate=%sysfunc(intnx(DAY,"&today"d,-1),date9.);
%end;
%else %do;
	%let startdate=%sysfunc(intnx(DAY,"&today"d,-1),date9.);
	%let enddate=%sysfunc(intnx(DAY,"&today"d,-1),date9.);
%end;
%put startdate= &startdate enddate= &enddate;
%mend;
%createdate;

proc sql;
create table order as
select id, createdate
from x.table
where "&startdate"d<= createdate <="&enddate"d;
quit;

[Reeza]

The INTNX option also supports the WEEKDAY interval, not just DAY interval. 

[Roddy]

I am receiving the below warning/error:

WARNING: Apparent symbolic reference startdate not resolved.

ERROR: Invalid date/time/datetime constant "&startdate"d.

ERROR: Invalid date/time/datetime constant "&enddate"d.

This is my exact code:

%macro datebound;
%let today=%sysfunc(today(),date9.);
%let day=%sysfunc(weekday("&today"d));

%if &day=2 %then %do;
%let startdate=%sysfunc(intnx(DAY,"&today"d,-3),date9.);
%let enddate=%sysfunc(intnx(DAY,"&today"d,-1),date9.);
%end;
%else %do;
%let startdate=%sysfunc(intnx(DAY,"&today"d,-1),date9.);
%let enddate=%sysfunc(intnx(DAY,"&today"d,-1),date9.);
%end;
%put startdate= &startdate enddate= &enddate;
%mend;
%datebound;

PROC SQL;
CREATE TABLE POLICY AS
SELECT
A.datebound,
A.Customer_ID,
A.InsFirstLast,
C.FirstName,
C.LastName,
A.email,
A.PlanCode,
A.Policy,
DATEPART(A.DateBound) AS FeedbackDate FORMAT MMDDYY10.,
A.DistributionChannel,
A.Carrier,
A.PlanType,
B.Supervisor,
B.Name
FROM
CorpAn.DirectNBPolicies A
LEFT JOIN CorpAn.Employee B ON A.I_EmpID = B.I_EmpID
LEFT JOIN AgCube.tbl_CustomerRelationShip C ON UPPER(A.InsFirstLast) = UPPER(C.FullName)
WHERE
"&startdate"d<= datebound <="&enddate"d
AND A.MediaCode NE '30'
AND A.email LIKE ('%@%')
;
QUIT;

[FreelanceReinh · Accepted solution]

To avoid the warning and error messages you should insert a %GLOBAL statement at the beginning of your macro:

%macro datebound;
%global startdate enddate;
...

Otherwise, the two macro variables are local to macro datebound and seize to exist once macro execution has finished.