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.
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
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
Sorry forgot to mention. start_of_wake_time and end_of_wake_time are already in numeric values with format of time8.
Remove the formats and check your data. The underlying data will likely explain the issue.
'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.').
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.').
You can refer to my first post's second table. They seem okay to me.
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
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?
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
Thank you so much! I'll keep rounding in mind for future codes.
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.
I see. Thank you for the example!
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.