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;
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.
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.
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;
The INTNX option also supports the WEEKDAY interval, not just DAY interval.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.