DATA Step, Macro, Functions and more

Using SAS dates to create variables that fall within a date range

Reply
Frequent Contributor
Posts: 78

Using SAS dates to create variables that fall within a date range

This is complicated to explain, but I need to create Yes/No (1/0) variables for each week from a certain startdate up to 150 weeks post startdate Week1-Week150.

The way I will determine Y/N for each of these weekly variables is I will have a start and end date for another variable (TRT).

All I need to do with this is determine if the TRT range of dates from start to finish falls into each week post startdate with a simple Yes or No. Even if the TRT range is only one day, I need to say YES for that week that it falls in and NO for all other weeks. However, the TRT range can span all 150 weeks.

Each subject will have a different startdate so the week range will be different for everyone.

Thanks for anyone's help!
Trusted Advisor
Posts: 2,116

Re: Using SAS dates to create variables that fall within a date range

Are you looking for 7 day periods after the start date or for calendar week (Sunday-Saturday)? The solutions are a bit different.

I'm assuming that you want the output vector to look like
00000011111110000000....
and not like
0000001111111000011111000....
(e.g. just one TRT period).
Frequent Contributor
Posts: 78

Re: Using SAS dates to create variables that fall within a date range

I'm looking for 7 day periods after the start date.

There will be more than one TRT period for some. So this time can start and stop multiple times. Here is a sample dataset:
ID BeginDate TRT_startdate TRT_enddate
01 01/01/2009 02/01/2009 04/01/2009
02 03/01/2009 03/01/2009 03/15/2009
02 03/01/2009 04/01/2009 06/01/2009
03 05/01/2009 05/07/2009 05/09/2009
03 05/01/2009 06/01/2009 10/01/2009
03 05/01/2009 10/15/2009 10/30/2009

For each ID, I need to create these week variables where Week1 = BeginDate+6days, Week2 = days 8 to 14, etc. The BeginDate is not changable for each subject, that remains constant.
In the end, I want the week variables just to be Yes or No to indicate if the TRT variables (TRT_startdate TRT_enddate) fall in those range of days.



Message was edited by: statadm
Super Contributor
Posts: 359

Re: Using SAS dates to create variables that fall within a date range

Use an array and check the dates :

array trtflg(150);
startdate = begindate;
flag = 0;
do i = 1 to 150;
stopdate = startdate + 7;
if startdate le trt_startdate lt stopdate then flag = 1;
trtflg(i) = flag;
if startdate le trt_enddate lt stopdate then flag = 0;
startdate = stopdate;
end;
Super Contributor
Super Contributor
Posts: 3,174

Re: Using SAS dates to create variables that fall within a date range

Agreed with Flip - it's very easy to check date-ranges using the INTNX function within a DO / END loop, such as:

DATA _NULL_;
FORMAT START_DT END_DT TEST_DT YOUR_DATE_VAR DATE9. ;
START_DT = INTNX('WEEK',TODAY(),-53);
END_DT = INTNX('WEEK',TODAY(),+53);
YOUR_DATE_VAR = MDY(1,1,2010);
DO TEST_DT = START_DT TO END_DT BY 7;
IF ( INTNX('WEEK',TEST_DT,0,'S') LE YOUR_DATE_VAR LE INTNX('WEEK',TEST_DT,0,'E') ) THEN DO;
* DO THIS CODE PARAGRAPH WHEN WITHIN WEEK-RANGE. ;
PUTLOG _ALL_;
END;
END;
RUN;


Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,177

Re: Using SAS dates to create variables that fall within a date range

Array handling can consume a lot of processing (for larger data volumes) but i/o has always been the issue for smaller platforms. For the systems where cpu time is under pressure, techniques have developed to deal with this kind of data model in different ways. For example this pseudo code would mark all weeks of a TRT (treatment?) in one statement, rather than mark each week of treatment separately :
clear buffer (of 150 weeks buffer= repeat( 'N', 149) )
do until last of customer/ID
get next event data
determine start week(e.g. int(stdate/7) )
determine end week (e.g. ceil(endDate/7) )
adjust start and end-week to be within the buffer( within the 150 weeks)
determine duration of event ( endw-startw )
flag buffer at startWeek for duration[pre] substr( buffer, startW, trt_durn ) = repeat( 'Y', trt_durn ) ;[/pre] repeat until last of customer/id
write buffer
this could involve overlaying the memory area of an array of 150 one character elements when if the flags need to be treated as separate variables.

I made no allowance for aligning week start to Sunday/Monday/startInterval, because I'm not sure how much impact it would have.
The model would be simpler and could operate at the day-level and wrap-up weeks later, if the TRTdate spans less than 89 years (1jan2000 +max buffer of days=32767) = 17Sep2089

If the target platform for the data model is not cpu constrained, then the methods offered by Scott and Flip may be the best alternative.

PeterC
Ask a Question
Discussion stats
  • 5 replies
  • 217 views
  • 0 likes
  • 5 in conversation