Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: Hour Function Not working Properly

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-26-2017 01:20 PM
(2646 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

sorry there is correction.

i used hour = hour(timepart(time))

i used hour = hour(timepart(time))

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

**Available on demand!**

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

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.