sort and calculate time difference in seconds by comparing to the line below

[rykwong]

hi SAS community

i would like to have a code to calculate the values in "duration".  THe dataset contains multiple patient MRI studies (Patient name).  Each MRI study contains a variable number of sequences (e.g a,b,c for patient 1, whereas a,b,c,d for patient 3).  Time start is the time that each sequence starts.  Duration is what I would like to calculate: which is calculated by subtracting the time start from the line below (after sorting the dataset in this order).  Obviously the data will have be to grouped by the MRI studies, so the last sequence of any given study does not have duration calculated (thus left blank) as we do not have the end time of the study.  Many thanks for your help in advance.

[ballardw · Accepted solution]

Since SAS provides functions to look at previous record values I would sort and run things in reverse order  then sort back.

proc sort data=have;
   by patientname descending time_start;
run;

data want;
   set have;
   duration=dif(time_start);
   by patientname;
   if first.patientname then duration=.;
run;

proc sort data=want;
   by patientname time_start;
run;

The Dif function is difference of the current record from the previous. Using By processing so make sure that the first dif does not use the value from the previous patient.

Warning: Do you have any of these sequences that cross midnight? Your actual time, if time of day, may be an issue. Next, is if your times are using a 12 hour clock and not a 24 hour clock then crossing noon might an issue since you have not actually provided any values in that time range.

[rykwong]

Great solution! Thanks so much

[rykwong]

no data that can cross midnight 🙂
but the data is in 12 hour clock and may cross noontime so I guess i must convert the times to 24 h clock as a solution, right?

[rykwong]

hi ballardw,

the code works but the numbers generated for duration are negative (note that seriestimestamp is the time_start)

thanks