SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

create consecutive intervals in months between 2 dates for all ids

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 124
Accepted Solution

create consecutive intervals in months between 2 dates for all ids

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.


Accepted Solutions
Solution
‎05-15-2017 01:53 AM
Community Manager
Posts: 565

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

I'm glad you found the solution,  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...

 

Best,

Anna

 

View solution in original post


All Replies
Super User
Posts: 19,822

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

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

Many users will not download Excel files. 

Frequent Contributor
Posts: 124

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

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

 
Frequent Contributor
Posts: 124

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

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.

Super User
Posts: 19,822

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

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

Frequent Contributor
Posts: 124

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

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.

 

 

 

Frequent Contributor
Posts: 124

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

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.

PROC Star
Posts: 1,167

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

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;

Frequent Contributor
Posts: 124

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

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.
Solution
‎05-15-2017 01:53 AM
Community Manager
Posts: 565

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

I'm glad you found the solution,  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...

 

Best,

Anna

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 228 views
  • 2 likes
  • 4 in conversation