update the date duration as Start and End

[Sathish_jammy]

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!

[PeterClemmensen · Accepted solution]

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

[andreas_lds]

Maybe i need more coffee to understand this, but please explain how to calculate the values of End_Dur. 

[Kurt_Bremser]

---

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

[Kurt_Bremser]

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.

[Kurt_Bremser]

You should seriously consider storing the result in a string and not a number, as with a number 2.1 equals 2.10.

[Ksharp]

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;