04282017 04:15 PM
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.
05122017 09:39 AM
I'm glad you found the solution, dr2014! I think this is the post with the solution you were referring to:
Best,
Anna
04282017 04:31 PM
Can you post a small subset of input and expected output, directly in the forum.
Many users will not download Excel files.
04282017 05:15 PM
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 
20100611 
20100823 
20100616 
44 
4 
18424 
20100611 
20100910 
20100911 
20101210 
1 

604 
20100611 
20100823 
20100623 
44 
5 
18424 
20100611 
20100910 
20100911 
20101210 
1 

604 
20100611 
20100823 
20100625 
2 
7 
18424 
20100611 
20100910 
20100911 
20101210 
1 

604 
20100611 
20100823 
20100625 
15 
8 
18424 
20100611 
20100910 
20100911 
20101210 
1 

604 
20100611 
20100823 
20100625 
93 
7 
18424 
20100611 
20100910 
20100911 
20101210 
1 

604 
20100611 
20100823 
20100625 
44 
8 
18424 
20100611 
20100910 
20100911 
20101210 
1 

604 
20100611 
20100823 
20100625 
73 
8 
18424 
20100611 
20100910 
20100911 
20101210 
1 
04282017 05:31 PM
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.
04282017 06:13 PM
Just to clarify, your intervals are unique for every record, so it's not like grouping data into specific quarters for analysis?
04282017 06:36 PM
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, 4month intervals are determined between the begin_date and final_date.
04282017 06:38 PM
I think I got what you are enquiring the final_date differs for all ids so the number of 4month intervals are different for each id.
04282017 09:48 PM
I'm kind of groping for what you want, but does this piece of code advance your progress? It calculates the number of complete fourmonth 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;
05122017 07:59 AM
05122017 09:39 AM
I'm glad you found the solution, dr2014! I think this is the post with the solution you were referring to:
Best,
Anna
Need further help from the community? Please ask a new question.