topic Global date statements - SAS EG 5.1 in SAS Enterprise Guide

Global date statements - SAS EG 5.1

EIrvin — Mon, 06 Feb 2017 16:38:37 GMT

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.

Re: Global date statements - SAS EG 5.1

Astounding — Mon, 06 Feb 2017 17:03:17 GMT

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.

Re: Global date statements - SAS EG 5.1

Tom — Mon, 06 Feb 2017 18:52:34 GMT

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.

Re: Global date statements - SAS EG 5.1

EIrvin — Mon, 06 Feb 2017 19:34:15 GMT

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.

Re: Global date statements - SAS EG 5.1

ChrisHemedinger — Mon, 06 Feb 2017 19:57:06 GMT

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