topic create consecutive intervals in months between 2 dates for all ids in SAS Data Management

create consecutive intervals in months between 2 dates for all ids

dr2014 — Fri, 28 Apr 2017 20:15:09 GMT

Hi all,

I really need your advice asap to create intervals of 3 months between 2 dates for all ids : begin_date and final_date. For the example provided (see file attached), 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 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 creatung the interval dates? 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 and do look at the attachment for the end result of the code below. I would like to know about a shorter way to accomplish the same.Thanks.

SAS CODE:

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 wanted to add that these intervals have to be determined for many others ids in the dataset.

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

Reeza — Fri, 28 Apr 2017 20:31:03 GMT

Can you post a small subset of input and expected output, directly in the forum.

Many users will not download Excel files.

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

dr2014 — Fri, 28 Apr 2017 21:15:30 GMT

Hi Reeza, I replied to your message ad even copy pasted the table and for some reason it didnt get posted. Hopefully this one gets out in time. Here is the final output with the original variables. The reason I have to calculate the inetrvals is to calcuate the average values of the event_value (which I have listed in the table below).

id	begin_date	final_date	event_date	event_number	event_value	begin_date2	int1start	int1end	int2start	int2end	p_Int1	p_Int2
604	2010-06-11	2010-08-23	2010-06-16	44	4	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1
604	2010-06-11	2010-08-23	2010-06-23	44	5	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1
604	2010-06-11	2010-08-23	2010-06-25	2	7	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1
604	2010-06-11	2010-08-23	2010-06-25	15	8	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1
604	2010-06-11	2010-08-23	2010-06-25	93	7	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1
604	2010-06-11	2010-08-23	2010-06-25	44	8	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1
604	2010-06-11	2010-08-23	2010-06-25	73	8	18424	2010-06-11	2010-09-10	2010-09-11	2010-12-10	1

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

dr2014 — Fri, 28 Apr 2017 21:31:40 GMT

Actually, I have an additon to my original question. I have to calculate the average values of the 'event_value' by 'event_number' in the dataset for all interval periods until the interval period that contains the final_date.

So what would be the best way to go about determining the interval periods and calculating the aaverage 'event_value' by 'event_number' for each interval.? I hope I am making sense here. Any help would be appreciated. Thanks.

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

Reeza — Fri, 28 Apr 2017 22:13:49 GMT

Just to clarify, your intervals are unique for every record, so it's not like grouping data into specific quarters for analysis?

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

dr2014 — Fri, 28 Apr 2017 22:36:05 GMT

For some reason Reeza I am not receiving emails to notify me when there is reply. Also, I actually made an error in my code. The interval period is for 4 months. But yes, the interval of 4 months is the same for all ids. I hope I an understanding your question correctly.

i.e for ids, 4-month intervals are determined between the begin_date and final_date.

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

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

I think I got what you are enquiring the final_date differs for all ids so the number of 4-month intervals are different for each id.

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

TomKari — Sat, 29 Apr 2017 01:48:16 GMT

I'm kind of groping for what you want, but does this piece of code advance your progress? It calculates the number of complete four-month intervals between begin_date and final_date, which is what you're doing in your code.

Tom

data have;

length begin_date final_date 8;

informat begin_date final_date date9.;

format begin_date final_date date9.;

input begin_date final_date;

cards;

11Jun2010 09Oct2010

11Jun2010 10Oct2010

11Jun2010 11Oct2010

11Jun2010 12Oct2010

11Jun2010 13Oct2010

11Jun2010 09Feb2011

11Jun2010 10Feb2011

11Jun2010 11Feb2011

11Jun2010 12Feb2011

11Jun2010 13Feb2011

11Jun2010 09Jun2011

11Jun2010 10Jun2011

11Jun2010 11Jun2011

11Jun2010 12Jun2011

11Jun2010 13Jun2011

run;

data want;

set have;

Intervals = intck('month4', begin_date, final_date, 'continuous');

run;

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

dr2014 — Fri, 12 May 2017 11:59:59 GMT

Thanks @TomKari. That was helpful to know the number of intervals. However, I got the solution I was looking for on the same post in the SAS data management group. I explained the concern clearly in that post.

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

AnnaBrown — Fri, 12 May 2017 13:39:49 GMT

I'm glad you found the solution, dr2014! I think this is the post with the solution you were referring to:

https://communities.sas.com/t5/Base-SAS-Programming/create-consecutive-intervals-in-months-between-2-dates-for-all/m-p/358206#M84152

Best,

Anna