Lapis Lazuli | Level 10

## update the date duration as Start and End

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: update the date duration as Start and End

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_Dur101	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```
6 REPLIES 6
Tourmaline | Level 20

## Re: update the date duration as Start and End

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_Dur101	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```

## Re: update the date duration as Start and End

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

Super User

## Re: update the date duration as Start and End

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

Super User

## Re: update the date duration as Start and End

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.

Super User

## Re: update the date duration as Start and End

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

Super User

## Re: update the date duration as Start and End

``````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;``````
Discussion stats
• 6 replies
• 633 views
• 1 like
• 5 in conversation