DATA Step, Macro, Functions and more

minute() function gives values off by 1 minute

Accepted Solution Solved
Reply
Highlighted
Occasional Contributor
Posts: 11
Accepted Solution

minute() function gives values off by 1 minute

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:

 

Picture 2.PNG

 

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;

Picture 1.PNG

 

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.


Accepted Solutions
Solution
a week ago
PROC Star
Posts: 7,539

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

Posted in reply to chingweelim

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

 

View solution in original post


All Replies
PROC Star
Posts: 7,539

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

Posted in reply to chingweelim

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

 

Occasional Contributor
Posts: 11

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

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

Super User
Posts: 20,236

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

Posted in reply to chingweelim

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

Occasional Contributor
Posts: 11

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

[ Edited ]

'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.').

Super User
Posts: 20,236

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

Posted in reply to chingweelim

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.').


 

Occasional Contributor
Posts: 11

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

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

Solution
a week ago
PROC Star
Posts: 7,539

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

Posted in reply to chingweelim

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

 

Occasional Contributor
Posts: 11

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

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?

PROC Star
Posts: 7,539

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

Posted in reply to chingweelim

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

 

Occasional Contributor
Posts: 11

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

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

Trusted Advisor
Posts: 1,068

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

Posted in reply to chingweelim

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.

Occasional Contributor
Posts: 11

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

I see. Thank you for the example! 

Super User
Posts: 11,578

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

Posted in reply to chingweelim

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 109 views
  • 0 likes
  • 5 in conversation