turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Hour Function Not working Properly

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:20 PM

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

Accepted Solutions

Solution

04-26-2017
02:11 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:43 PM - edited 04-26-2017 01:46 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:23 PM

sorry there is correction.

i used hour = hour(timepart(time))

i used hour = hour(timepart(time))

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:34 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

04-26-2017 01:42 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:44 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

04-26-2017 01:46 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:49 PM

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.

Solution

04-26-2017
02:11 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:43 PM - edited 04-26-2017 01:46 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:44 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:49 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:54 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 01:59 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 03:46 PM

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.