Desktop productivity for business analysts and programmers

Global date statements - SAS EG 5.1

Reply
Occasional Contributor
Posts: 10

Global date statements - SAS EG 5.1

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.

Super User
Posts: 5,365

Re: Global date statements - SAS EG 5.1

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.

Super User
Super User
Posts: 6,845

Re: Global date statements - SAS EG 5.1

[ Edited ]

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.

 

Occasional Contributor
Posts: 10

Re: Global date statements - SAS EG 5.1

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.
Community Manager
Posts: 2,889

Re: Global date statements - SAS EG 5.1

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

Ask a Question
Discussion stats
  • 4 replies
  • 152 views
  • 4 likes
  • 4 in conversation