BookmarkSubscribeRSS Feed
jnivi
Calcite | Level 5

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
Kurt_Bremser
Super User

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;
s_lassen
Meteorite | Level 14

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;

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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