Calcite | Level 5

## Calculating difference in dates for different records with the same ID

Hi there,

I am trying to calculate the difference in dates among records with the same ID.  I would like the derived variable to look similar to the one below.

ID        Date             DateDiff (derived new variable)

1         01JAN21                 9

1        10JAN21                  9

2      05FEB22                 0

2     05FEB22                 0

Any help is much appreciated, thank you!

2 REPLIES 2
Super User

## Re: Calculating difference in dates for different records with the same ID

Do you always have two observations per id, or can there be more? If there can be more, which difference should be taken?

One way:

proc sql;
create table want as
select
id,
date,
max(date) - min(date) as datediff
from have
group by id
;
quit;
Meteorite | Level 14

## Re: Calculating difference in dates for different records with the same ID

Given data like this

data have;
format id 8. date date9.;
informat date date.;
input id date;
cards;
1 01jan21
1 10jan21
2 5feb21
2 5feb21
;run;

you can use code like this

data want;
set have;
by id;
datediff=dif(date);
if first.id the datediff=.;
run;

The code will work if your dates are SAS date values, as shown in the example.

- although I am not quite sure why your example data show the difference to the next record for the first record in each group.

If that is really what you want, it can be done like this:

data want;
set want;
by id;
if first.id and not last.id then do;
_N_=_N_+1;
set want(keep=datediff) point=_N_;
end;
run;

Discussion stats
• 2 replies
• 253 views
• 0 likes
• 3 in conversation