topic EPOCH to second conversion in SAS Programming

EPOCH to second conversion

dkassis — Mon, 17 Feb 2020 23:25:02 GMT

Hi,

I am trying to convert the below from sql to SAS code in my proc sql statement. Any ideas? It seems to not like the '% 86400' when converting to seconds.

proc sql;
create table AMI_test as

select lp.epoch_interval_time,

((lp.epoch_interval_time -7*3600) % 86400) div 3600 as intv_hr,
(((lp.epoch_interval_time -7*3600) % 86400) div 3600) +1 as intrvl_24hr_ending

Thanks,

Dan

Re: EPOCH to second conversion

ChrisNZ — Tue, 18 Feb 2020 00:26:02 GMT

3600 is the number of seconds in an hour, so you are dealing with datetimes (counted in seconds).

So you don't need to convert to seconds.

% is the modulo operator right?

divide ( mod ( lp.epoch_interval_time - 7*3600 , 86400 ), 3600 ) as intv_hr,

should do what you want.

Re: EPOCH to second conversion

dkassis — Tue, 18 Feb 2020 01:00:56 GMT

Thanks, that worked!

Re: EPOCH to second conversion

Tom — Tue, 18 Feb 2020 01:07:26 GMT

What do you think the percent is doing as an operator in that expression? Is it MOD ? SAS has a MOD() function.

What are those "magic" numbers?

What is 3600? Is it the number of seconds in an hour? 60sec/min*60min/hr=3,600sec/hr.

If you want the number of seconds in an hour use '01:00:00't

What is 86400? Is it the number of seconds in a day? 24hr/day*3,600sec/hr=86,400sec/hr

If you want the number of seconds in 24 hours use '24:00:00't

But what is the formula trying to actually do? Is it subtracting 7 hours? Taking the time since midnight and then hours since midnight?

hour(datepart(intnx('hour',lp.epoch_interval_time,-7,'same')))

Re: EPOCH to second conversion

ChrisNZ — Tue, 18 Feb 2020 02:20:18 GMT

The mod() takes out all the (full) days, then subtract 7 hours, then transform time to (decimal) day.

I agree that these numbers could be better shown as proper durations such as '01:00:00't rather than the underlying count.

The intent here is to change unit though, so maybe showing the raw figure makes this intent more obvious. Maybe.

This might be the best balance?

divide ( mod ( lp.epoch_interval_time - '07:00:00't, '24:00:00't), 3600 ) as INTV_HR