Dear Experts,
I have to calculate the date duration in the below-given format.
Let me share the sample dataset for a clear view.
data have;
input Id r_date :mmddyy10. e_date :mmddyy10.;
format r_date e_date mmddyy10.;
cards;
101 05/18/2001 09/25/2002
101 09/26/2002 12/12/2003
101 12/13/2003 05/04/2004
108 06/06/1995 08/12/1996
108 08/11/1996 07/10/1999
run;
For this data set, I need to process the date duration in the given method.
Expected output.
Note : The value of Start_dur in each minimum R_date of IDs is always 0
Each E_dur will calculate the duration of the minimum R_date of corresponding IDs.
Please suggest some ideas to solve the task.
Thanks in Advance!
I'm not sure, I get this completely, though here's a shot. I don't understand why End_Dur should be three in the last obs in the ID=101 group though?
data want;
if 0 then set have;
Start_Dur=0;
do until (last.id);
set have;
by id;
End_Dur=Start_Dur+round(intck('day', r_date, e_date)/365.25, .1);
output;
Start_Dur=End_Dur;
end;
run;
Result:
Id r_date e_date Start_Dur End_Dur
101 05/18/2001 09/25/2002 0 1.4 101 09/26/2002 12/12/2003 1.4 2.6 101 12/13/2003 05/04/2004 2.6 3 108 06/06/1995 08/12/1996 0 1.2 108 08/11/1996 07/10/1999 1.2 4.1
I'm not sure, I get this completely, though here's a shot. I don't understand why End_Dur should be three in the last obs in the ID=101 group though?
data want;
if 0 then set have;
Start_Dur=0;
do until (last.id);
set have;
by id;
End_Dur=Start_Dur+round(intck('day', r_date, e_date)/365.25, .1);
output;
Start_Dur=End_Dur;
end;
run;
Result:
Id r_date e_date Start_Dur End_Dur
101 05/18/2001 09/25/2002 0 1.4 101 09/26/2002 12/12/2003 1.4 2.6 101 12/13/2003 05/04/2004 2.6 3 108 06/06/1995 08/12/1996 0 1.2 108 08/11/1996 07/10/1999 1.2 4.1
Maybe i need more coffee to understand this, but please explain how to calculate the values of End_Dur.
@andreas_lds wrote:
Maybe i need more coffee to understand this, but please explain how to calculate the values of End_Dur.
The decimals are the number of months, so you get a meaningless result when stored as a number (because 0.10 is the same as 0.1).
It takes a little trickery to build the number, but here we go:
data have;
input Id r_date :mmddyy10. e_date :mmddyy10.;
format r_date e_date yymmddd10.;
cards;
101 05/18/2001 09/25/2002
101 09/26/2002 12/12/2003
101 12/13/2003 05/04/2004
108 06/06/1995 08/12/1996
108 08/11/1996 07/10/1999
run;
data want;
set have;
by id;
retain start_dur end_dur start_date;
format start_dur end_dur best5.;
if first.id
then do;
start_dur = 0;
start_date = r_date;
end;
else do;
start_dur = end_dur;
end;
dur = intck('month',start_date,e_date,'c');
end_dur = input(catx('.',put(int(dur/12),3.),put(mod(dur,12),2.)),5.);
drop start_date dur;
run;
proc print data=want noobs;
run;
Result:
start_ Id r_date e_date dur end_dur 101 2001-05-18 2002-09-25 0 1.4 101 2002-09-26 2003-12-12 1.4 2.6 101 2003-12-13 2004-05-04 2.6 2.11 108 1995-06-06 1996-08-12 0 1.2 108 1996-08-11 1999-07-10 1.2 4.1
Mind that such a number isn't much good for further use, as 2.11 is smaller than 2.2.
You should seriously consider storing the result in a string and not a number, as with a number 2.1 equals 2.10.
data have;
input Id r_date :mmddyy10. e_date :mmddyy10.;
dif=round(yrdif(r_date,e_date,'age'),.1);
format r_date e_date yymmddd10.;
cards;
101 05/18/2001 09/25/2002
101 09/26/2002 12/12/2003
101 12/13/2003 05/04/2004
108 06/06/1995 08/12/1996
108 08/11/1996 07/10/1999
;
run;
data want;
set have;
by id;
if first.id then end_dur=0;
end_dur+dif;
start_dur=lag(end_dur);
if first.id then start_dur=0;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.