topic Re: minute() function gives values off by 1 minute in SAS Programming

minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 01:25:06 GMT

I am puzzled as to why some of the results given by the minute() function are off by 1 minute during data step.

Below is the have data:

Below is the code and the want data:

data want;
set have;
start_wake_minute = minute(Start_of_Wake_Time);
end_wake_minute = minute(End_of_Wake_Time);
run;

As you can see in the first row, 'end_wake_minute' shows 59 when it is supposed to be 00. This is the same for the next two rows. The last two rows gave correct values. Am I missing something?

I have also included 'start_wake_time_num' and 'end_wake_time_num' where they are numeric formats of 'Start of Wake Time' and 'End of Wake Time' respectively for your reference.

Re: minute() function gives values off by 1 minute

art297 — Fri, 08 Dec 2017 01:50:33 GMT

You didn't provide your input file in the form of a datastep and, my guess, that is where your problem is.

Assuming row 2 should have a value of 30, the following does exactly what you showed you wanted:

data have;
  input (start_of_wake_time end_of_wake_time) (time8.);
  cards;
5:00:00 22:00:00
5:00:00 23:30:00
6:00:00 22:00:00
3:10:00 21:08:00
7:00:00 22:10:00
;
data want;
  set have;
  start_wake_minute = minute(Start_of_Wake_Time);
  end_wake_minute = minute(End_of_Wake_Time);
run;

Art, CEO, AnalystFinder.com

Re: minute() function gives values off by 1 minute

Reeza — Fri, 08 Dec 2017 02:00:23 GMT

Remove the formats and check your data. The underlying data will likely explain the issue.

Re: minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 02:07:45 GMT

Sorry forgot to mention. start_of_wake_time and end_of_wake_time are already in numeric values with format of time8.

Re: minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 02:13:42 GMT

'start_wake_time_num' and 'end_wake_time_num' are 'start_of_wake_times' and 'end_of_wake_times' numeric values respectively with the 'time8.' format removed (replaced with 'best12.').

Re: minute() function gives values off by 1 minute

Reeza — Fri, 08 Dec 2017 02:16:33 GMT

And what do they look like?

@chingweelim wrote:

'start_wake_time_num' and 'end_wake_time_num' are 'start_of_wake_times' and 'end_of_wake_times' numeric values respectively with the 'time8.' format removed (replaced with 'best12.').

Re: minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 02:55:54 GMT

You can refer to my first post's second table. They seem okay to me.

Re: minute() function gives values off by 1 minute

art297 — Fri, 08 Dec 2017 03:01:17 GMT

Try the following on your data and let us (the forum) know if it produces the result you expect:

data want;
  set have;
  start_wake_minute =minute(Round(Start_of_Wake_Time,hms(0,1,00)));
  end_wake_minute =minute(Round(End_of_Wake_Time,hms(0,1,00)));
run;

Art, CEO, AnalystFinder.com

Re: minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 03:50:02 GMT

Brilliant! I tried it and it worked wonders! I am just curious as to why we need to round. The formatted best12. values seems alright to me. Is it because it may have decimal places involved? Just an FYI, I've imported these times from excel. So that could have caused small decimal values to be included while importing?

Re: minute() function gives values off by 1 minute

art297 — Fri, 08 Dec 2017 03:59:05 GMT

No idea without actually seeing the raw data or the data with no format applied but, yes, it could well be because the raw data contained fractions of a second. The code I posted simply rounded the time to the nearest minute.

Art, CEO, AnalystFinder.com

Re: minute() function gives values off by 1 minute

mkeintz — Fri, 08 Dec 2017 04:03:01 GMT

The time8..0 format rounds to the nearest minutes. Consider:

data _null_;
   t='21:59:59.8't;
   put t=time10.1;
   put t=time8.0;
   minute_t=minute(t);
   put minute_t=;
run;

which produces:


t=21:59:59.8
t=22:00:00
minute_t=59

Because the time8. format rounded up, you were unaware that the time value was still in the 59th minute.

Re: minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 04:53:27 GMT

I see. Thank you for the example!

Re: minute() function gives values off by 1 minute

chingweelim — Fri, 08 Dec 2017 04:54:17 GMT

Thank you so much! I'll keep rounding in mind for future codes.

Re: minute() function gives values off by 1 minute

ballardw — Fri, 08 Dec 2017 16:43:14 GMT

@chingweelim wrote:

Brilliant! I tried it and it worked wonders! I am just curious as to why we need to round. The formatted best12. values seems alright to me. Is it because it may have decimal places involved? Just an FYI, I've imported these times from excel. So that could have caused small decimal values to be included while importing?

Decimals in a SAS time value are fractions of second.

Likely Excel is most likely hiding the fractional seconds with a display that only shows whole seconds. Go to the spreadsheet document and change the column to simple numeric with 2 or 3 decimals and examine. I suspect you will see the same decimals as SAS shows. There might be differences if you have enough decimals, more than 10 most likely, that precision of computer storage issues arise.