topic Re: Calculating missing time in SAS Procedures

Calculating missing time

ebonyw — Tue, 24 Feb 2015 16:22:19 GMT

I have data from medical providers that documents the time they recorded temperatures in their vaccine storage units. They are supposed to record temps 24 hrs a day to make sure their refrigerators and freezers maintain appropriate temps so vaccines are not compromised. I know things happen where they may forget to plug their digital thermometers back after reading recordings or maybe have power failures...so they have a little leeway so 24 hours or more of missing recording time not good.

What would be the syntax to determine how much time is missing between a provider's storage unit record times?

Date/Time Start	Date/Time End	Missing Time (New Variable)
9/27/13 9:15	9/28/13 12:00	------
9/28/13 12:15	9/28/13 13:15	Time from the end of their recording time to when they started recording again...14 minutes the provider didn't record
9/29/13 15:15	9/30/13 11:30	26 hours the provider did not record temperatures

Re: Calculating missing time

Reeza — Tue, 24 Feb 2015 16:25:56 GMT

Are the first two columns what your data looks like or does your colour coding indicate something about the data?

Re: Calculating missing time

ebonyw — Tue, 24 Feb 2015 16:30:20 GMT

Sorry, Reeza. I was just trying to make it clear what was being used to calculate the missing time. The coloring truly doesn't mean anything. Sorry for the confusion. 🙂

Re: Calculating missing time

Reeza — Tue, 24 Feb 2015 16:38:22 GMT

The duration for the second record is 1 hour. Where does 14 minutes come from?

Re: Calculating missing time

PaigeMiller — Tue, 24 Feb 2015 18:09:56 GMT

How is this different than your other thread?

https://communities.sas.com/thread/71757

How could anyone or anything determine "missing time" from the information provided?

Re: Calculating missing time

ebonyw — Tue, 24 Feb 2015 18:18:44 GMT

Hi Paige - these are two seperate questions. The first one that you helped me on, I needed to calculate total record time (duration) from the start/end times provided. This question, however, is asking how to figure out the time in between each recording. So, for example, the first line shows the doctor measure temps for a little over 26 hours. He unplugged his digital thermometer and didn't hook it back up until 12:15 on 9/28/13 (the second line)....14 minutes later (stopped recording at 12:00, began recording again at 12:15...14 minutes that are not accounted for). I can't figure out what SAS code to use that will calculate the missing time from one line to the next. Hope that makes sense! 😞 From your comment above, if there isn't a way to determine missing time from the information provided, then that is good to know, as well.

Re: Calculating missing time

ebonyw — Tue, 24 Feb 2015 18:23:27 GMT

Reeza - The 14 minutes is the difference in time from the 1st line's ending and the 2nd line's start time. This doctor unplugged his thermometer/ended recording at 12:00 on 9/28/13 (END time for the first line). He started recording again at 12:15 on 9/28/13 (second line's START time). There was 14 minutes that he did have temperature data because his thermometer was not recording. Hope that makes sense. Thanks so much for your thoughts. I feel like pulling my hair out. Ahhh!

Re: Calculating missing time

PaigeMiller — Tue, 24 Feb 2015 18:38:00 GMT

Ok, I get it, so you want to subtract the end time on line 1 from the start time on line 2.

Same method works as in the other thread, once you have taken care of the issue that the data isn't on the same line.

Try something like this (assuming these are actual datetime values)

data abc;

set have;

prev_end_time=lag(end_time);

missing_time=start_time - prev_end_time;

format missing_time time.;

run;

Re: Calculating missing time

ebonyw — Tue, 24 Feb 2015 18:44:23 GMT

THANK YOU SOOOOO much, Paige. Yes...you understood my lengthy explanation correctly. 🙂 I'm going to try this now. Thank you!!!!!

Re: Calculating missing time

Reeza — Tue, 24 Feb 2015 18:48:20 GMT

Use the lag function, see the code below. Then use if/then calculations based on that variable.

Please note that the duration from 00 to 15 is 15 minutes, if you need 14 minutes as you've indicated you'll need to use a correction factor.

If your variables are SAS Datetime variables, time_unplugged, will be noted in seconds and you'll need to convert that to minutes/hours as required or use the hhmm format applied. Otherwise, as indicated in your other questions you'll need to convert it to a SAS datetime.

data have;

informat time_start time_end datetime20.;

format time_: datetime21.;

input time_start time_end;

cards;

27SEP13:09:15:00 28SEP13:12:00:00

28SEP13:12:15:00 28SEP13:13:15:00

29SEP13:15:15:00 30SEP13:11:30:00

;

run;

data want;

set have;

time_unplugged=time_start-lag(time_end);

format time_unplugged hhmm7.;

run;

proc print data=want;

run;

Re: Calculating missing time

ebonyw — Tue, 24 Feb 2015 18:57:43 GMT

Thank you so much, Reeza!!!!