topic Re: Selecting a Date range in SAS Procedures

Selecting a Date range

deleted_user — Mon, 04 Oct 2010 18:51:57 GMT

I run a weekly report in SAS 9.1 that tracks data from the previous Monday to the current Sunday. For example, if I ran it today, the ranges would be: 9/27/10 to 10/3/10.

I am trying to figure out a way to have these dates generate automatically. I can not figure out how to get the Monday thru Sunday date range. The trouble is sometimes the Monday will be from the previous month, and the Sunday date will be in the current month (like the above date sample).

Is there a way within the intnx function that I can have the Monday date be generated every week? The closest I can get is:
sdate=intnx('weekday','01sep2010'd,-2)

That's fine for september, but not if I want the report to run automatically every month.

Re: Selecting a Date range

RickM — Mon, 04 Oct 2010 20:09:22 GMT

You could maybe do

end=today()-weekday(today())+1;
start=end-6;

Good luck!

Re: Selecting a Date range

deleted_user — Mon, 04 Oct 2010 20:28:11 GMT

> You could maybe do
>
> end=today()-weekday(today())+1;
> start=end-6;
>
> Good luck!

That worked nicely. But...

If I use what you suggested next Monday, will I get 10/4/10 for Monday and 10/11/10 for Sunday's date?

Re: Selecting a Date range

Cynthia_sas — Mon, 04 Oct 2010 20:53:12 GMT

Hi:
I suppose there's a more elegant way to do this with INTNX and shift intervals, but at least I can explain this logic.
Using INTNX with the WEEK interval and 'BEGIN' and an increment of 0
[pre]
intnx('week', date, 0, 'begin');
[/pre]

will always take you to a SUNDAY. The trick is that on SUNDAY, you want the MONDAY from the previous week and on MON-SAT, you want the monday from the current week. The program below, I think, does what you want. As I said, there probably is a more elegant way to do it with INTNX. But as you can see from the output in the log, when the date used with INTNX is Oct 1, then the Monday created by this logic is Sept 27.

cynthia
[pre]7942 data testit;
7943 do date='15sep2010'd to '15oct2010'd;
7944 ** intnx of 'week' with 'begin' always goes to SUNDAY;
7945 wdate=intnx('week', date, 0, 'begin');
7946
7947 ** so the wdate + 1 is going to be MONDAY if the day of week is Monday-Saturday;
7948 ** when the day of week is SUNDAY, then you want the PREVIOUS Monday.;
7949 day_of_week = weekday(date);
7950 if day_of_week =1 then monday = wdate-6;
7951 else monday = wdate +1;
7952 putlog date= weekdate28. day_of_week= wdate= weekdate28. monday= weekdate28. ;
7953 if day_of_week = 1 then putlog ' ';
7954 end;
7955 run;

date=Wednesday, Sep 15, 2010 day_of_week=4 wdate=Sunday, Sep 12, 2010 monday=Monday, Sep 13, 2010
date=Thursday, Sep 16, 2010 day_of_week=5 wdate=Sunday, Sep 12, 2010 monday=Monday, Sep 13, 2010
date=Friday, Sep 17, 2010 day_of_week=6 wdate=Sunday, Sep 12, 2010 monday=Monday, Sep 13, 2010
date=Saturday, Sep 18, 2010 day_of_week=7 wdate=Sunday, Sep 12, 2010 monday=Monday, Sep 13, 2010
date=Sunday, Sep 19, 2010 day_of_week=1 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 13, 2010

date=Monday, Sep 20, 2010 day_of_week=2 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 20, 2010
date=Tuesday, Sep 21, 2010 day_of_week=3 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 20, 2010
date=Wednesday, Sep 22, 2010 day_of_week=4 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 20, 2010
date=Thursday, Sep 23, 2010 day_of_week=5 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 20, 2010
date=Friday, Sep 24, 2010 day_of_week=6 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 20, 2010
date=Saturday, Sep 25, 2010 day_of_week=7 wdate=Sunday, Sep 19, 2010 monday=Monday, Sep 20, 2010
date=Sunday, Sep 26, 2010 day_of_week=1 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 20, 2010

date=Monday, Sep 27, 2010 day_of_week=2 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 27, 2010
date=Tuesday, Sep 28, 2010 day_of_week=3 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 27, 2010
date=Wednesday, Sep 29, 2010 day_of_week=4 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 27, 2010
date=Thursday, Sep 30, 2010 day_of_week=5 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 27, 2010
date=Friday, Oct 1, 2010 day_of_week=6 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 27, 2010
date=Saturday, Oct 2, 2010 day_of_week=7 wdate=Sunday, Sep 26, 2010 monday=Monday, Sep 27, 2010
date=Sunday, Oct 3, 2010 day_of_week=1 wdate=Sunday, Oct 3, 2010 monday=Monday, Sep 27, 2010

date=Monday, Oct 4, 2010 day_of_week=2 wdate=Sunday, Oct 3, 2010 monday=Monday, Oct 4, 2010
date=Tuesday, Oct 5, 2010 day_of_week=3 wdate=Sunday, Oct 3, 2010 monday=Monday, Oct 4, 2010
date=Wednesday, Oct 6, 2010 day_of_week=4 wdate=Sunday, Oct 3, 2010 monday=Monday, Oct 4, 2010
date=Thursday, Oct 7, 2010 day_of_week=5 wdate=Sunday, Oct 3, 2010 monday=Monday, Oct 4, 2010
date=Friday, Oct 8, 2010 day_of_week=6 wdate=Sunday, Oct 3, 2010 monday=Monday, Oct 4, 2010
date=Saturday, Oct 9, 2010 day_of_week=7 wdate=Sunday, Oct 3, 2010 monday=Monday, Oct 4, 2010
date=Sunday, Oct 10, 2010 day_of_week=1 wdate=Sunday, Oct 10, 2010 monday=Monday, Oct 4, 2010

date=Monday, Oct 11, 2010 day_of_week=2 wdate=Sunday, Oct 10, 2010 monday=Monday, Oct 11, 2010
date=Tuesday, Oct 12, 2010 day_of_week=3 wdate=Sunday, Oct 10, 2010 monday=Monday, Oct 11, 2010
date=Wednesday, Oct 13, 2010 day_of_week=4 wdate=Sunday, Oct 10, 2010 monday=Monday, Oct 11, 2010
date=Thursday, Oct 14, 2010 day_of_week=5 wdate=Sunday, Oct 10, 2010 monday=Monday, Oct 11, 2010
date=Friday, Oct 15, 2010 day_of_week=6 wdate=Sunday, Oct 10, 2010 monday=Monday, Oct 11, 2010
NOTE: The data set WORK.TESTIT has 1 observations and 4 variables.
[/pre]

Re: Selecting a Date range

deleted_user — Fri, 15 Oct 2010 12:48:07 GMT

cynthia

Sorry for the delayed response. I have tested this and it worked. I'm going to apply it to my data and go from there.

Thanks for the response.
-Alana-

Re: Selecting a Date range

data_null__ — Fri, 15 Oct 2010 13:49:36 GMT

I'm not sure I understand your use of previous and current, and the rest. But you can use a shifted date interval to define a WEEK that starts on any day you like using WEEK.2 to create 1 week intervals that start on MONDAY.

[pre]
94 data _null_;
95 file log ls=255;
96 do date='15sep2010'd to '15oct2010'd;
97 if weekday(date) eq 2 then put;
98 sdate=intnx('week.2',date,0,'begin');
99 edate=intnx('week.2',date,0,'end');
100 putlog (date sdate edate)(+2 weekdate28.);
101 end;
102 run;

Wednesday, Sep 15, 2010 Monday, Sep 13, 2010 Sunday, Sep 19, 2010
Thursday, Sep 16, 2010 Monday, Sep 13, 2010 Sunday, Sep 19, 2010
Friday, Sep 17, 2010 Monday, Sep 13, 2010 Sunday, Sep 19, 2010
Saturday, Sep 18, 2010 Monday, Sep 13, 2010 Sunday, Sep 19, 2010
Sunday, Sep 19, 2010 Monday, Sep 13, 2010 Sunday, Sep 19, 2010

Monday, Sep 20, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010
Tuesday, Sep 21, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010
Wednesday, Sep 22, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010
Thursday, Sep 23, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010
Friday, Sep 24, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010
Saturday, Sep 25, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010
Sunday, Sep 26, 2010 Monday, Sep 20, 2010 Sunday, Sep 26, 2010

Monday, Sep 27, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010
Tuesday, Sep 28, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010
Wednesday, Sep 29, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010
Thursday, Sep 30, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010
Friday, Oct 1, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010
Saturday, Oct 2, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010
Sunday, Oct 3, 2010 Monday, Sep 27, 2010 Sunday, Oct 3, 2010

Monday, Oct 4, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010
Tuesday, Oct 5, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010
Wednesday, Oct 6, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010
Thursday, Oct 7, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010
Friday, Oct 8, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010
Saturday, Oct 9, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010
Sunday, Oct 10, 2010 Monday, Oct 4, 2010 Sunday, Oct 10, 2010

Monday, Oct 11, 2010 Monday, Oct 11, 2010 Sunday, Oct 17, 2010
Tuesday, Oct 12, 2010 Monday, Oct 11, 2010 Sunday, Oct 17, 2010
Wednesday, Oct 13, 2010 Monday, Oct 11, 2010 Sunday, Oct 17, 2010
Thursday, Oct 14, 2010 Monday, Oct 11, 2010 Sunday, Oct 17, 2010
Friday, Oct 15, 2010 Monday, Oct 11, 2010 Sunday, Oct 17, 2010
[/pre]

Re: Selecting a Date range

Hima — Thu, 02 Feb 2012 20:44:42 GMT

PROC SQL;
CREATE TABLE TEST AS
SELECT *
FROM TABLENAME WHERE DATE BETWEEN (TODAY() - 😎 AND (TODAY() -1);
QUIT;