topic Re: create consecutive intervals in months between 2 dates for all ids in SAS Programming

create consecutive intervals in months between 2 dates for all ids

dr2014 — Fri, 28 Apr 2017 19:45:22 GMT

Hi all,

I need your advice asap to create intervals of 3 months between 2 dates : begin_date and final_date. For the example provided, the 2 dates are within 3 months of each other, but I could have the final_date in the analysis anywhere from 1 to 12 or more months apart.

The example provided is for an id=604. It has begin_date, final_date, event_number, event_date. All the dates had a format of yymmdd10. I first converted the 'begin_date' into a sas date callled 'begin_date2' to use the intnx function. Below is my code:

I created 2 start and end dates ..int1start, int1end, int2start, int2end and 2 intervals p_int1 and p_int2 . I gave the interval a value of '1' or '0' : '1' if the final_date was between the start and end dates for the interval and '0' if the final_date was absent between the start and end dates for the interval.

I would like to know if there is faster way to do this. How can I ask for the final_date to determine the 'last end date' for the 'last interval? Basically, how can stop creating the interval dates just after I reach the final_date? The final_date has to be within the last interval period. Also, can I create these interval start and end dates using macro and also the intervals p_int1 etc.? Please let me know. I attached the end result of the code below. I would like to know about a shorter way to accomplish the same.Thanks.

begin_date2=begin_date; /* converted into a sas date*/

int1start=begin_date2 ;

int1end=intnx('month',begin_date2,3,'same')-1 ;

int2start=intnx('month',int1end,0,'same')+1 ;

int2end=intnx('month',int2start,3,'same')-1 ;

format int1start int1end int2start int2end yymmdd10.;

if int1start le final_date_new lt int1end then p_Int1=1 ;

else if int2start le final_date_new lt int2end then p_Int2=1 ;

format int1start int1end int2start int2end yymmdd10.;

I also have determine these intervals for many others ids in the original dataset.

Look forward to your replies. Thanks.

Re: create consecutive intervals in months between 2 dates for all ids

ArtC — Fri, 28 Apr 2017 22:55:53 GMT

Your basic approach - use of INTNX - is fine. I simplified one of the statements (INT2START) a bit.

One concern with the use of the 'SAME' alignment operator is if a date is on the end of the month. The start date in the second row is at the end of November; watch what it does to the February date.

data have;
input (begin_date final_date_new)(date9.);
datalines;
11jun201023aug2010
30nov200930jan2010
run;

data want;
set have;
int1start=begin_date ;
int1end=intnx('month',int1start,3,'same')-1 ;
int2start=intnx('month',int1start,3,'same');
/*int2start=intnx('month',int1end,0,'same')+1 ;*/
int2end=intnx('month',int2start,3,'same')-1 ;
if int1start le final_date_new lt int1end then p_Int1=1 ;
else if int2start le final_date_new lt int2end then p_Int2=1 ;
format begin_date final_date_new int1start int1end int2start int2end yymmdd10.;
run;

Also only one FORMAT statement is needed.

Re: create consecutive intervals in months between 2 dates for all ids

dr2014 — Fri, 28 Apr 2017 23:42:02 GMT

Thanks for your reply Reeza and the correction on the datastep. But there is more to this. I probablly didn't do a good job explaining. The final_date keeps diffreing for each id. I need to know of a way where I can stop creating intervals for each id until I reach the final_date.

There is a possibility that the final_date could be in the 4th interval or the 5th interval. How can I automatically create 4 month intervals until the final_date is reached or in other words in within the last inerval period of that id?

My intention is to calculate the avearge event_value for each id in each of its intervals by event_number.

I hope this explains my concern well. I porbably may not need the last if, then else statements too.

Please let me know your ideas on this.

Re: create consecutive intervals in months between 2 dates for all ids

dr2014 — Fri, 28 Apr 2017 23:47:31 GMT

Thanks also for the tip on the 'same' operator. What would be an alternative way of doing it then to aviod that?

Re: create consecutive intervals in months between 2 dates for all ids

ArtC — Sat, 29 Apr 2017 01:18:14 GMT

The real question is the definition of the interval. Perhaps this behavior is fine. We do not know at this point because we do not really know your interval definitions.

Re: create consecutive intervals in months between 2 dates for all ids

ArtC — Sat, 29 Apr 2017 01:20:49 GMT

Are you saying that there may be up to 5 intervals not just the two that you coded for and that the FINAL_DATE determines the termination of the intervals?

Re: create consecutive intervals in months between 2 dates for all ids

dr2014 — Sun, 30 Apr 2017 22:41:48 GMT

Yes, thats right. The intervals can range from 1 to 8 and the final_date determines the last interval. . I have to define these intervals for each id in the dataset. The interval periods are for 4 months.

so for example here are the dates for id 1 and 2:

id	Begin_date	Final_date
1	2010-10-04	2011-04-04
2	2010-03-01	2011-11-02

So I need to create 4 month intervals between these 2 date variables and stop creating them when I reach the final_date, where the final_date is within the last interval i.e final_date is less than the last interval 'end_date' or it could be the 'end-date' of the last interval.

I need to do this for each id. Can this be done automatically using arrays or other date options?

Re: create consecutive intervals in months between 2 dates for all ids

ArtC — Wed, 03 May 2017 06:10:49 GMT

Here is an array solution with 4 month intervals.

data have;
input id @3 begin_date date9.
         @13 final_date_new date9.;
datalines;
1 11jun2010 23aug2011
2 30nov2009 30nov2012
3 30nov2009 30nov2016
run;

data want(drop=int);
set have;
array strtdate {8} intstart1 - intstart8;
array stopdate {8} intend1 - intend8;

int=1;
strtdate{int}=begin_date ;
stopdate{int}=min(intnx('month',strtdate{int},4,'same')-1 ,final_date_new);
do while(stopdate{int} lt final_date_new and int lt 8);
   int+1;
   strtdate{int}=intnx('month',strtdate{int-1},4,'same') ;
   stopdate{int}=min(intnx('month',strtdate{int},4,'same')-1 ,final_date_new);
end;

format begin_date final_date_new intstart: intend: yymmdd10.;
run;
title want;
proc print data=want;
run;

The interval start and stop variables have been renamed for ease of coding. The DO WHILE is evaluated at the top of the loop so it can be exited as soon as the criteria (exceed the end date or 8 intervals) is met.

Re: create consecutive intervals in months between 2 dates for all ids

dr2014 — Fri, 12 May 2017 11:56:16 GMT

Thanks @ArtC for the solution.