BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dr2014
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

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.

View solution in original post

8 REPLIES 8
ArtC
Rhodochrosite | Level 12

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.

dr2014
Quartz | Level 8

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.

dr2014
Quartz | Level 8

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

 

ArtC
Rhodochrosite | Level 12
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.
ArtC
Rhodochrosite | Level 12
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?
dr2014
Quartz | Level 8

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?

ArtC
Rhodochrosite | Level 12

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.

dr2014
Quartz | Level 8
Thanks @ArtC for the solution.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2874 views
  • 0 likes
  • 2 in conversation