Hour Function Not working Properly

[ysk]

Hi I am using date/time variable called time and have used the following to extract the hour.

Time = 12AUG2016:08:00:00 

Hour = timepart(time) 

But the result shows 7 instead of 8. 

I noticed that this happens to any time that has no minutes and seconds in them. If minutes and seconds are inputted it is pulling it correctly. For example, when time =  12AUG2016:08:02:00 the hour function outputs 8 and not 7 

I am wondering what could be causing this error.

Thanks

[ysk]

sorry there is correction.

i used hour = hour(timepart(time))

[Rick_SAS]

Are you sure that the datetime value is correct?When I run the following program in SAS 9.4m4, I get '8', which is correct:

data DT;
format time datetime20.;
Time = '12AUG2016:08:00:00'dt;
Hour = hour(timepart(time) );
run;
proc print; run;

[ysk]

Hi Rick,

Yes i have time variable formatted as DATETIME19. and i used hour = hour(timepart(time)) and still show as 7 instead of 8.

Not sure if importing the data may result in this error but I'm importing the data from excel with the following 

proc import out= data
datafile="epr.xlsx"
dbms=excel replace;
getnames=yes;
mixed=yes;
usedate=no;
scantime=yes;
run;

This is frustrating because the function is used appropriately but the result is not showing as it's intended to be.

[Rick_SAS]

Are you saying that you run my program and get a different result?  If so, what version of SAS are you running?

[ysk]

No i didn't run your program. Your program shows up as 8 but for some reason my data despite having the same formatting show up as 7.

[Rick_SAS]

Then the problem is your data, not the HOUR function. Make sure you are importing the data correctly.

You can increase the decimal width of the DATETIME format to see if it is a rounding issue:

format time datetime32.10;

Use tom's solution to round the data, if necessary.

[Tom · Accepted solution]

Sounds like a rounding issue.  The HOUR() function is ignoring the minutes/seconds, but the display format is rounding.

You could use the ROUND(dt,1) to round your datetime value to the nearest second.  Or you could use ROUND(dt,60) to round to the nearest minute.

data _null_;
  input time time20. ;
  dt = dhms(today(),0,0,time);
  hour = hour(dt);
  hour_second = hour(round(dt,1));
  hour_minute = hour(round(dt,60));
  hour_hour  = hour(round(dt,60*60));
  format dt datetime20. time time12.3;
  put (dt time hour:) (=);
cards;
07:59:59.999
07:59:59
07:30:30
07:25:00
;

dt=26APR2017:08:00:00 time=7:59:59.999 hour=7 hour_second=8 hour_minute=8 hour_hour=8
dt=26APR2017:07:59:59 time=7:59:59.000 hour=7 hour_second=7 hour_minute=8 hour_hour=8
dt=26APR2017:07:30:30 time=7:30:30.000 hour=7 hour_second=7 hour_minute=7 hour_hour=8
dt=26APR2017:07:25:00 time=7:25:00.000 hour=7 hour_second=7 hour_minute=7 hour_hour=7

[ysk]

but i used timepart(time) and it shows up as 8:00:00 but when i use hour(time) it shows up as 7. 

Should i be trying to round up the variable time?

[Tom]

Yes. You should round. Display you value using fractional seconds to see the actual value. Or display it as a number to see the fractional seconds.

Also you do not need to use the TIMEPART() funciton. The HOUR() function can work on both DateTime values like you have and pure TIME values like what TIMEPART() produces since both are numbers of seconds.

[ysk]

Round_second seems to do the trick. But what is happening in SAS that is causing it to read it as 7 instead of 8?

[Rick_SAS]

My guess is that your datum is slightly less than 8:00. When you use the DATETIME format, it LOOKS like the time is 8:00 because it gets rounded, but in reality, the time is less than 8:00. Here is an example that seems to fit your description:

data DT;
format time datetime19.;
Time = '12AUG2016:07:59:59.999999'dt;
Hour = hour(time);
run;
proc print; run;

[ballardw]

Look in your data in the Excel sheet and change the cell format for a couple of those cells to numeric with about 5 decimals. If you see decimal values and the last digits before the decimal are not a multiple of 60 then the Excel display format has been rounding the appearance but not the values.

When the data was brought into SAS then the entire value is available and detectable with the hour/minute/second functions.