Calculating difference in minutes between two time stamps

[Shad]

Hi,

I have a question regarding time. formatted data and trying to calculate the minutes between the arrival of a patient and the arrival of members of their care team. Part of the issue is dealing with time that crosses midnight, and in some cases where the team member was present before the patient arrived. 

Here is some example data

data question;
  input ID $ Arrival_Time time. TT_member_arrived time.  ;
format Arrival_Time time. TT_member_arrived time. ;
cards;
4077114 . 17:02:00
4077114 . 17:15:00
4077114 . 17:15:00
4077242 23:56:00 .
4077242 23:56:00 0:09:00
4077242 23:56:00 0:06:00
4077318 0:22:00 0:10:00
4077318 0:22:00 0:28:00
4077318 0:22:00 0:28:00
4077486 17:50:00 .
4077486 17:50:00 18:00:00
1776022 19:20:00 20:18:00
1776022 19:20:00 20:37:00
1776022 19:20:00 20:27:00
1776022 19:20:00 .
1776022 19:20:00 .
4078301 0:35:00 .
4078301 0:35:00 0:36:00
4078439 13:33:00 14:18:00
4078439 13:33:00 16:50:00
4078439 13:33:00 .
4078439 13:33:00 .
4078753 4:06:00 4:20:00
4078753 4:06:00 4:05:00
4078753 4:06:00 4:05:00
1813347 23:40:00 1:14:00
1813347 23:40:00 1:14:00
1813347 23:40:00 .
4079401 14:03:00 15:30:00
4079401 14:03:00 .
4079401 14:03:00 .
9980300 21:46:00 21:51:00
9980300 21:46:00 22:20:00
9980300 21:46:00 22:12:00
9980300 21:46:00 .
9980300 21:46:00 .
9980300 21:46:00 .
9980300 21:46:00 .
1975827 20:05:00 .
1975827 20:05:00 22:30:00
1975827 20:05:00 22:30:00
4079529 21:13:00 21:13:00
4079529 21:13:00 21:20:00
4079529 21:13:00 21:20:00
1828163 20:09:00 .
1828163 20:09:00 22:30:00
1828163 20:09:00 22:30:00
1828163 20:09:00 .
1676588 0:14:00 0:30:00
1676588 0:14:00 .
;
run;

I attempted to use the following code to tabulate the number of minutes either (negative minutes for a team member being early) positive for the number of elapsed minutes until a member arrived. 

    diff_in_min = intck("minute", Arrival_Time, TT_member_arrived);

  if TT_member_arrived lt Arrival_Time then duration=('24:00:00't-Arrival_Time)+TT_member_arrived;
  else duration=TT_member_arrived-Arrival_Time;
  
  duration_min = (duration/60);

Here is a screenshot of the result: 

regardless of the method I tried either using intck or calculating duration didn't quite work. For instance row 5, I want the output to be the time between 23:56 and 0:09 or 13 minutes elapsed. Calculating the duration kind of worked except in cases where the team member was present before the patient arrived, for instance row 7, should be negative 12.

Any suggestions? 

Thanks,  

[PaigeMiller]

Suggestion: don't eliminate the date from the time. If you have the actual dates and times, so the variables you read in are now valid SAS date/time variables, then INTCK ought to work to keep track of when the time crosses midnight into the next day, and you will get the proper result.

[Shad]

The only date that is available is the arrival date, however it's a separate variable. I assume I would need both the date/time for arrival_time and TT_member_arrived or would this would with just the start date/time? 

[Tom · Accepted solution]

If you had actual TIMESTAMP value (timestamp is an alias for DATETIME) then you midnight problem goes away.

Can you get a better source that did not eliminate the DAY part from the DATETIME values?

Otherwise just make up your own rule about when to consider the difference as negative versus close to 24 hours.  Kind of like the Year Cutoff that SAS uses for dealing with date values that have and the century removed.

So if you pick 30 minutes your code might look like:

data want;
  set question ;
  next_day = (Arrival_Time -TT_member_arrived) > '00:30:00't ;
  duration = next_day*'24:00:00't + TT_member_arrived - Arrival_Time;
  minutes = duration / 60 ;
  format duration time10. minutes comma6.2 ;
run;

[Shad]

Unfortunately, there is no better data source. I do have arrival date but it's a separate variable and there is no TT_member_arrived date available. But this work around is great. I think I got it working with a 12hour cut off. 

Thank you! 

[Kurt_Bremser]

Can you be a 100% sure that there will never, ever be a duration of more than 24 hours?

[Shad]

For this instance, I think it's a safe assumption that there would not be an instance longer than 24 hours, yes. 

[AMSAS]

Ignoring the fact that you have missing data for arrival and TT Member arrival times, here's one approach 

data have ;
	input ID $ Arrival_Time time. TT_member_arrived time.  ;
	format Arrival_Time time. TT_member_arrived time. ;
	if Arrival_Time<TT_member_arrived  then
		diffSecs=TT_member_arrived-Arrival_Time ;
	else 
		diffSecs="24:00"t-Arrival_Time+TT_member_arrived ;
	diffMins=diffSecs/60 ;

cards;
4077114 . 17:02:00
4077114 . 17:15:00
4077114 . 17:15:00
4077242 23:56:00 .
4077242 23:56:00 0:09:00
4077242 23:56:00 0:06:00
4077318 0:22:00 0:10:00
4077318 0:22:00 0:28:00
4077318 0:22:00 0:28:00
4077486 17:50:00 .
4077486 17:50:00 18:00:00
1776022 19:20:00 20:18:00
1776022 19:20:00 20:37:00
1776022 19:20:00 20:27:00
1776022 19:20:00 .
1776022 19:20:00 .
4078301 0:35:00 .
4078301 0:35:00 0:36:00
4078439 13:33:00 14:18:00
4078439 13:33:00 16:50:00
4078439 13:33:00 .
4078439 13:33:00 .
4078753 4:06:00 4:20:00
4078753 4:06:00 4:05:00
4078753 4:06:00 4:05:00
1813347 23:40:00 1:14:00
1813347 23:40:00 1:14:00
1813347 23:40:00 .
4079401 14:03:00 15:30:00
4079401 14:03:00 .
4079401 14:03:00 .
9980300 21:46:00 21:51:00
9980300 21:46:00 22:20:00
9980300 21:46:00 22:12:00
9980300 21:46:00 .
9980300 21:46:00 .
9980300 21:46:00 .
9980300 21:46:00 .
1975827 20:05:00 .
1975827 20:05:00 22:30:00
1975827 20:05:00 22:30:00
4079529 21:13:00 21:13:00
4079529 21:13:00 21:20:00
4079529 21:13:00 21:20:00
1828163 20:09:00 .
1828163 20:09:00 22:30:00
1828163 20:09:00 22:30:00
1828163 20:09:00 .
1676588 0:14:00 0:30:00
1676588 0:14:00 .
;
run;