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 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.

1 ACCEPTED SOLUTION

Accepted Solutions
AnnaBrown
Community Manager

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

 


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

View solution in original post

9 REPLIES 9
Reeza
Super User

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

Many users will not download Excel files. 

dr2014
Quartz | Level 8

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

 
dr2014
Quartz | Level 8

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.

Reeza
Super User

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

dr2014
Quartz | Level 8

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.

 

 

 

dr2014
Quartz | Level 8

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.

TomKari
Onyx | Level 15

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;

dr2014
Quartz | Level 8
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.
AnnaBrown
Community Manager

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

 


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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