BookmarkSubscribeRSS Feed
EIrvin
Fluorite | Level 6

Good Day all!

 

I have hit a quandry that I am having some difficulty finding a workable solution outside of hard-coding dates.  I run a daily report where I compare Month-to-date to Prior Month-to-date and Prior Year Month-to-date. I have succesfully written out global statements to identif these ranges to where when the year rolls over there is no need to change any coding.  However, my quandry is looking at week-to-date and prior week-to-date.  SAS identifies Sunday as the beginning of the week, however, my need is to run the week Monday- Sunday.  In addition to that caveat, I need a solution to keep the beginning of the week at Monday then compare this week to date to last week to date.  For example if today is Wednesday, Current Week-to-date should show the date ranges of this Monday to this Wednesday and the prior week-to-date to show last Monday through last Wednesday.  The dates that the program is pulling is based on dates that are preformatted ddmmmyyyy.  The global statements I have baked into my program work wonderfully and accurately, and they are shown below.

/*CURRENT DATE*/
%LET CD=INTNX('DAY',TODAY(),-1);
/*PREVIOUS YEAR START DATE*/
%LET PY=INTNX('YEAR',&CD,-1);
/*PREVIOUS YEAR MONTH START DATE*/
%LET PYM=INTNX('MONTH',&CD,-12);
/*PREVIOUS YEAR TO DATE*/
%LET PYTD=INTNX('YEAR',&CD,-1,'SAMEDAY');
/*CURRENT YEAR START DATE*/
%LET CY=INTNX('YEAR',INTNX('YEAR',&CD,1),-1);
/*PREVIOUS MONTH START DATE*/
%LET PM=INTNX('MONTH',&CD,-1);
/*PREVIOUS MONTH TO DATE*/
%LET PMTD=INTNX('MONTH',&CD,-1,'SAMEDAY');
/*CURRENT MONTH START DATE*/
%LET CM=INTNX('MONTH',INTNX('MONTH',&CD,1),-1);

Any thoughts or suggestions would be greatly appreciated.

4 REPLIES 4
Astounding
PROC Star

It's difficult to confirm the accuracy of your current statements.  Macro language doesn't apply INTNX, for example, so there must be code somewhere in a DATA step (or perhaps within a SQL SELECT) that uses your macro variables, something like:

 

d1 = &cd;

d2 = &py;

 

Since I can't see that code, I'll have to take your word for it that it is functioning well. 

 

To approach your current problem, I would suggest the WEEKDAY function applied to the current date.  It can tell you enough to calculate the first day of the current week.  Then to get the previous week, subtract 7 from the beginning and end of the current week.

Tom
Super User Tom
Super User

To do the same with WEEKS instead of months change your interval from 'MONTH' to 'WEEK' . To make the weeks start on Monday use 'WEEK.2'.

 

Something like

/*PREVIOUS WEEK START DATE*/ 
%LET PW=INTNX('WEEK.2',&CD,-1); 
/*PREVIOUS WEEK TO DATE*/ 
%LET PWTD=INTNX('WEEK.2',&CD,-1,'SAMEDAY'); 
/*CURRENT WEEK START DATE*/ 
%LET CW=INTNX('WEEK.2',&CD,0); 

 

Also note that weeks are always 7 days long. So the PWTD can just be calculated directly from &CD. 

%LET PWTD=&CD-7;

 

You do realize that your macro variables do not contain the actual dates. Instead they have the code that can be run to calculate the dates.

 

EIrvin
Fluorite | Level 6

Thank you, this is what I needed, however, when I tried to run this in a copy of teh program, I received the following error, which I have not seen on my other %let statements, but where I placed the CW and PW codes caused all of the other %let statements to fail as well.   Any idea on how to combat this?

 

16         /*CURRENT DATE*/
17         %LET CD=INTNX('DAY',TODAY(),-1);
18         /*CURRENT WEEK START DATE*/
19         %LET CW=INTNX('WEEK.2',&CD,0)
20         /*PRIOR WEEK START DATE*/
21         %LET PW=INTNX('WEEK.2',&CD,-1);
ERROR: Open code statement recursion detected.
22         /*PRIOR WEEK TO DATE*/
23         %LET PWTD=INTNX('WEEK.2',&CD,-1,'SAMEDAY')
ERROR: Open code statement recursion detected.
24         /*PREVIOUS YEAR START DATE*/
25         %LET PY=INTNX('YEAR',&CD,-1);
26         /*PREVIOUS YEAR MONTH START DATE*/
27         %LET PYM=INTNX('MONTH',&CD,-12);
ERROR: Open code statement recursion detected.
28         /*PREVIOUS YEAR TO DATE*/
29         %LET PYTD=INTNX('YEAR',&CD,-1,'SAMEDAY');
ERROR: Open code statement recursion detected.
30         /*CURRENT YEAR START DATE*/
31         %LET CY=INTNX('YEAR',INTNX('YEAR',&CD,1),-1);
ERROR: Open code statement recursion detected.
32         /*PREVIOUS MONTH START DATE*/
33         %LET PM=INTNX('MONTH',&CD,-1);
ERROR: Open code statement recursion detected.
34         /*PREVIOUS MONTH TO DATE*/
35         %LET PMTD=INTNX('MONTH',&CD,-1,'SAMEDAY');
ERROR: Open code statement recursion detected.
36         /*CURRENT MONTH START DATE*/
37         %LET CM=INTNX('MONTH',INTNX('MONTH',&CD,1),-1);
ERROR: Open code statement recursion detected.
ChrisHemedinger
Community Manager

You're missing a semicolon at the end of line 19.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 892 views
  • 4 likes
  • 4 in conversation