BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Roddy
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

 

View solution in original post

5 REPLIES 5
FreelanceReinh
Jade | Level 19

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
Lapis Lazuli | Level 10

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
Super User

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

Roddy
Calcite | Level 5

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
Jade | Level 19

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.

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 3926 views
  • 1 like
  • 4 in conversation