BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AMFR
Quartz | Level 8

Hello SAS experts,

 

I have a numeric datetime variable like this 18AUG2015:16:25:00. I want to round this to bottom hour. Output should be like 18AUG2015:16:00:00. How can i do this. Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

INTNX function with alignment 'B' for begin:

data example;
   dt = '18AUG2015:16:25:00'dt;
   y  = intnx('hour',dt,0,'B');
   format y datetime.;
   put y= datetime.;
run;

View solution in original post

11 REPLIES 11
ballardw
Super User

INTNX function with alignment 'B' for begin:

data example;
   dt = '18AUG2015:16:25:00'dt;
   y  = intnx('hour',dt,0,'B');
   format y datetime.;
   put y= datetime.;
run;
AMFR
Quartz | Level 8
Thanks ballardw for your reply. I tried with your piece of code. There is
one problem, I have a huge dataset and with your code SAS is running from
last 5 minutes. I can see in the log that it worked and created
the timestamp variable the way I wanted. But it is running and running and
not producing output in the results window.

Thanks

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Reeza
Super User

Remove the PUT statement. There will be no OUTPUT in the results window because you're creating a data set and there is no 'results'. 

 

You can also use the ROUND function, but I doubt that will help. Datetime are stored in seconds, so the nearest hour is the nearest, 

60seconds*60minutes=3600 seconds. 

 

Time = round(time_var, 3600);
AMFR
Quartz | Level 8

Thanks Reeza.

 I tried round function but it didnt produce the desired results. But after removing the PUT statement i got the desired results. I do not why i was expexting output in the results window in a data step, that was such a lame question. I think I am lost. Thanks again

ballardw
Super User

@AMFR wrote:

Thanks Reeza.

 I tried round function but it didnt produce the desired results. But after removing the PUT statement i got the desired results. I do not why i was expexting output in the results window in a data step, that was such a lame question. I think I am lost. Thanks again


AND the original reason I had PUT was for demostration. I expected you to only use the function as needed in your actual code.

 

I believe the issue with @Reeza's suggestion was using ROUND which would round up when seconds exceed 1800 with in any hour  interval. Floor might work but you'd need to do more arithemetic to get the right trim and with the existing solution likely not worth headache.

AMFR
Quartz | Level 8
Thanks for the details, I appreciate it
imdickson
Quartz | Level 8

@ballardw Hi.

My value:

18Aug2018 12:31:00

17Aug2018 01:28:00

 

What If i want to set if minutes => 30 then round up + 1 hour.

Otherwise, use your sample code to set to 00 minutes.

 

Currently your code always set minutes to 00 regardless of the minutes.

Tom
Super User Tom
Super User

If you actually did want to round then use the ROUND() function.  Since DATETIME values are seconds you can use the number of seconds in an hour as the second argument to ROUND().  So just use a time literal.

round(dt,'01:00't)

Example: 

105   data example;
106     do dt = '18AUG2015:16:25:00'dt,'18AUG2015:16:35:00'dt ;
107      y  = intnx('hour',dt,0,'B');
108      z = round(dt,'01:00't);
109      format dt y z datetime20.;
110      put (_all_) (=);
111     end;
112   run;

dt=18AUG2015:16:25:00 y=18AUG2015:16:00:00 z=18AUG2015:16:00:00
dt=18AUG2015:16:35:00 y=18AUG2015:16:00:00 z=18AUG2015:17:00:00

 

imdickson
Quartz | Level 8

I havent reach my workdesk yet.

Assuming i want it to only round up or down for 30mins.

Changing from

round(dt,'01:00't)

to

round(dt,'00:30't)

 

will do the trick right?

@Tom

Tom
Super User Tom
Super User

Not sure what "bottom hour" means. Does that mean when the minute hand is pointing down at the 6?  Do you want to convert '06:25' and '06:35' to '06:00' or '06:30'?

AMFR
Quartz | Level 8

I meant to  convert '06:25' and '06:35' to '06:00'?

 

Thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 7498 views
  • 4 likes
  • 5 in conversation