topic Re: Correctly calculating hours between two times in SAS Programming

Correctly calculating hours between two times

RyanSimmons — Thu, 02 May 2019 16:58:27 GMT

I have a dataset full of times, indicating when an individual went to bed at night and when they woke up the next morning. I want to create a new variable that indicates the number of hours that they were asleep. However, SAS doesn't seem to understand how clocks work, and is unable to "wrap" times around the clock to do the calculation in a sensible way.

For example, if someone went to bed at 9pm and woke up at 5am, we would WANT the value of hours slept to be 8. However, SAS will return +/- 16, since it is only able to calculate the difference in a linear fashion. There are a couple of workarounds to this (included in my code snippet below), but they feel very clunky to me. One of them is pulling in the date variables and using the dhms function and then converting the result into hours, the other is converting the bedtime variable into the # of hours before midnight and then adding that to the wake up time variable.

Here's an example:

DATA time;
	/* Bed time and wakey time */
	bed_time = 75600; format bed_time time8.;
	wake_time = 18000; format wake_time time8.;

	/* Wrong answer */
	wrong_hours_slept = intck('hour',bed_time,wake_time);

	/* Clunky solution 1 */
	day1 = today(); format day1 date8.;
	day2 = intnx('day',day1,1); format day2 date8.;
	seconds_slept = dhms(day2,0,0,wake_time) - dhms(day1,0,0,bed_time);
	right_hours_slept1 = seconds_slept/3600;

	/* Clunky solution 2 */
	hours_before_midnight = (86400 - bed_time)/3600;
	right_hours_slept2 = hours_before_midnight + wake_time/3600;
run;

Are there any other solutions? Obviously both of the above work, and ultimately don't require too much extra coding, but it feels rather frustrating to me that SAS doesn't have a built-in way of manipulating clock time. Further, both of these clunky solutions also have points of failure (notably with people who go to sleep after midnight), requiring some if/then logic to correct.

Re: Correctly calculating hours between two times

Reeza — Thu, 02 May 2019 17:15:14 GMT

Are you aware of any implementation in any programming language that does account for times that cross days? Genuinely curious.

Regarding SAS, you need to add the dates to get the times to be handled correctly. There's no way around that.

Ideally you'd have the dates included in the data not just time and use a datetime variables - just subtracting them would solve the problem, which you're already determined.

data want;
set have;

sleep_start = dhms(date_start, 0, 0, time_start);
sleep_end = dhms(date_end, 0, 0, time_end);

Hours_slept = sleep_end - sleep_start / (60*60); *result is in seconds, convert to hours;

run;

Re: Correctly calculating hours between two times

novinosrin — Thu, 02 May 2019 17:18:20 GMT

I really like your question. Since the understanding largely involves number of seconds since midnight(time) vs number of seconds from Jan1,1960(Datetime)

Your approach to create a datetime value and then compute arithmetic to create hours seems to be the only thing or right thing to do.

Considering the case of sleeping hours mandates datetime values, I wonder how otherwise this can be done, i.e besides the scope of having a convoluted expression like

data want;
bed_time = 75600; format bed_time time8.;
wake_time = 18000; format wake_time time8.;
hours_slept = intck('hour', dhms(today()-1,0,0,bed_time),dhms(today(),0,0,wake_time));
run;

Re: Correctly calculating hours between two times

RyanSimmons — Thu, 02 May 2019 17:48:23 GMT

Unfortunately I'm not well-versed in many programming languages, so I can't say for sure. I thought that the "lubridate" package in R had that functionality, but I may be mistaken. I know too that some R functions for fitting longitudinal or time series models (for example, the temporal smoothers in the "mgcv" package) have ways of taking the "wrapping around" of time or date variables into account, but the application is different.

On one hand, it makes sense why this is limitation exists, since the typical programming solution is to encode times as the number of seconds relative to some benchmark (e.g. midnight). On the other hand, it's a very simple mathematical operation, so it would surprise me if there were NO solutions to this. Even something as simple as having an option to the "intck" function that says "crossdays=TRUE" or something to that effect.

Anyway, thank you for your response! It looks like I am just going to have to code something clunky (unfortunately I don't have date variables for both days, I only have date variables for the first day, and some people went to sleep past midnight, so I am going to have to do an if/then loop or two to make the calculations work for everyone).

Re: Correctly calculating hours between two times

FreelanceReinh — Thu, 02 May 2019 17:51:09 GMT

Hi @RyanSimmons,

How about this?

right_hours_slept = 24*(bed_time>wake_time)+(wake_time-bed_time)/3600;

Note that your right_hours_slept1 and right_hours_slept2 values (unlike the above expression) would need a correction if someone went to bed after midnight.

Re: Correctly calculating hours between two times

RyanSimmons — Thu, 02 May 2019 18:55:35 GMT

Interesting! Clever idea. Looks like this is essentially the same logic as my "clunky solution 2", but operationalized better. I'll leave this question open for a little while longer to see if anybody else comes up with something clever, otherwise I think this will be the answer!

Re: Correctly calculating hours between two times

PGStats — Thu, 02 May 2019 20:16:16 GMT

SAS has all the tools needed for time calculations. The idea is to stay away from the internal representation of data and time values with date and time literals. In time literals, a day is '24:00:00't and an hour is '1:00:00't.

DATA time;
	/* Bed time and wakey time */
	bed_time = '21:00:00't; format bed_time time9.;
	wake_time = '24:00:00't + '05:00:00't; format wake_time time9.;

	hours_slept = (wake_time - bed_time) / '1:00:00't;
run;

or if you prefer

DATA time;
	/* Bed time and wakey time */
	bed_time = '21:00:00't; format bed_time time9.;
	wake_time = '05:00:00't; format wake_time time9.;

	hours_slept = ('24:00:00't + wake_time - bed_time) / '1:00:00't;
run;

Re: Correctly calculating hours between two times

FreelanceReinh — Thu, 02 May 2019 21:07:59 GMT

Here's a shorter expression which avoids the repetition of variable names:

right_hours_slept = mod(24+(wake_time-bed_time)/3600,24);

Re: Correctly calculating hours between two times

repchur — Thu, 25 Mar 2021 17:41:28 GMT

Hello! This really helped me and allowed me to calculate the correct number of hours between two times. However, I am having difficulty picking the code apart. Would you be able to explain each argument in some more detail to help me understand?

Thanks so much in advance for your consideration.

Re: Correctly calculating hours between two times

FreelanceReinh — Thu, 25 Mar 2021 22:54:07 GMT

Hello @repchur,

Glad to hear that my old post helped you, too.

Basically, we had to apply one of two formulas to compute variable right_hours_slept:

(wake_time-bed_time)/3600 if bed_time is "at or after midnight." In this case both times belong to the same date and wake_time>bed_time. Therefore, dividing the (positive) difference of the SAS time values (i.e., numbers of seconds after midnight of that same date) by 3600 gives us the time difference in hours rather than seconds.
24+(wake_time-bed_time)/3600 if bed_time is "before midnight." In this case we assume that bed_time and wake_time belong to two consecutive days and that bed_time>wake_time. Therefore, variable right_hours_slept can be computed as the sum of the time (in hours) from bed_time until midnight, which is 24-bed_time/3600, and the time from midnight until wake_time, which is wake_time/3600, resulting in said formula.

What happens if we (incorrectly) apply the formula of case 2 to case 1? The correct, positive number of hours slept (for example, 7.5) is increased by 24 (result: 31.5 in the example).

Now the function f(x)=mod(x, 24) comes to the rescue because for a non-negative value x, written in the form 24*n+r with a non-negative integer n and a remainder r with 0<=r<24, it yields r. (Divide x by 24 to obtain the integer part n of the quotient and the remainder r, both uniquely determined.) That is, any additional integer multiple of 24, be it 24, 48 or 72, etc., is deducted while the remainder is left unchanged: f(x)=f(r)=r for all n=0, 1, 2, ...

In our application of f to the expression 24+(wake_time-bed_time)/3600 only two different values of n occur: n=0 for the correct result in case 2 (remember that wake_time-bed_time is negative here) and n=1 when the formula of case 2 is applied to case 1. So, the effect of function f just amounts to subtracting the incorrectly added 24 (hours) in the latter case, while the correct result in the former case is left unchanged. Hence we've found a single formula that is applicable to case 1 and case 2:

right_hours_slept = mod(24+(wake_time-bed_time)/3600,24)