topic Re: Calculating difference in minutes between two time stamps in SAS Programming

Calculating difference in minutes between two time stamps

Shad — Mon, 08 May 2023 20:44:06 GMT

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,

Re: Calculating difference in minutes between two time stamps

PaigeMiller — Mon, 08 May 2023 20:54:43 GMT

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.

Re: Calculating difference in minutes between two time stamps

Tom — Mon, 08 May 2023 22:22:10 GMT

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;

Re: Calculating difference in minutes between two time stamps

Shad — Mon, 08 May 2023 23:24:40 GMT

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?

Re: Calculating difference in minutes between two time stamps

Shad — Mon, 08 May 2023 23:26:33 GMT

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!

Re: Calculating difference in minutes between two time stamps

Kurt_Bremser — Tue, 09 May 2023 04:42:18 GMT

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

Re: Calculating difference in minutes between two time stamps

Shad — Tue, 09 May 2023 17:27:48 GMT

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

Re: Calculating difference in minutes between two time stamps

AMSAS — Tue, 09 May 2023 17:39:45 GMT

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;