BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ysk
Calcite | Level 5 ysk
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

12 REPLIES 12
ysk
Calcite | Level 5 ysk
Calcite | Level 5
sorry there is correction.

i used hour = hour(timepart(time))
Rick_SAS
SAS Super FREQ

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;

Region Capture.png

ysk
Calcite | Level 5 ysk
Calcite | Level 5

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
SAS Super FREQ

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

ysk
Calcite | Level 5 ysk
Calcite | Level 5
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
SAS Super FREQ

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
Super User Tom
Super User

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
Calcite | Level 5 ysk
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5 ysk
Calcite | Level 5
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
SAS Super FREQ

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
Super User

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.

 

 

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2653 views
  • 2 likes
  • 4 in conversation