Hi!
I have these data
18JUL2016:03:08:01
18JUL2016:03:23:40
18JUL2016:03:39:16
18JUL2016:03:54:51
18JUL2016:04:10:30
18JUL2016:04:26:08
18JUL2016:04:41:43
18JUL2016:04:57:20
18JUL2016:05:13:03
18JUL2016:05:28:40
18JUL2016:05:44:15
18JUL2016:05:59:53
18JUL2016:06:15:28
18JUL2016:06:31:07
18JUL2016:06:46:42
18JUL2016:07:02:17
18JUL2016:07:17:55
18JUL2016:07:33:31
18JUL2016:07:49:14
18JUL2016:08:04:51
18JUL2016:08:20:45
18JUL2016:08:36:27
18JUL2016:08:52:05
and I wanted to get the maximum time on each hour. May I know the ways to solve this?
Thanks!
This:
data have;
input datetimevalue datetime19.;
format datetimevalue datetime19.;
cards;
18JUL2016:03:08:01
18JUL2016:03:23:40
18JUL2016:03:39:16
18JUL2016:03:54:51
18JUL2016:04:10:30
18JUL2016:04:26:08
18JUL2016:04:41:43
18JUL2016:04:57:20
18JUL2016:05:13:03
18JUL2016:05:28:40
18JUL2016:05:44:15
18JUL2016:05:59:53
18JUL2016:06:15:28
18JUL2016:06:31:07
18JUL2016:06:46:42
18JUL2016:07:02:17
18JUL2016:07:17:55
18JUL2016:07:33:31
18JUL2016:07:49:14
18JUL2016:08:04:51
18JUL2016:08:20:45
18JUL2016:08:36:27
18JUL2016:08:52:05
;
run;
data intermediate;
set have;
date = datepart(datetimevalue);
format date date9.;
hour = hour(datetimevalue);
run;
data want;
set intermediate;
by date hour;
if last.hour;
run;
proc print noobs;
run;
produces this result:
datetimevalue date hour 18JUL2016:03:54:51 18JUL2016 3 18JUL2016:04:57:20 18JUL2016 4 18JUL2016:05:59:53 18JUL2016 5 18JUL2016:06:46:42 18JUL2016 6 18JUL2016:07:49:14 18JUL2016 7 18JUL2016:08:52:05 18JUL2016 8
If you get something different, show code and log, please.
data intermediate;
set have;
date = datepart(datetimevalue);
hour = hour(datetimevalue);
run:
data want;
set intermediate;
by date hour;
if last.hour;
run;
Hi!
Thanks for replying. But when I'm submiting the code I only get the very last hour not the maximum time of each hour and when I'm removing the "if last.hour" of the code, I'm getting the exact output.
If your data is sorted correctly the last record per hour should be the largest.
Please explain how this is not working.
This:
data have;
input datetimevalue datetime19.;
format datetimevalue datetime19.;
cards;
18JUL2016:03:08:01
18JUL2016:03:23:40
18JUL2016:03:39:16
18JUL2016:03:54:51
18JUL2016:04:10:30
18JUL2016:04:26:08
18JUL2016:04:41:43
18JUL2016:04:57:20
18JUL2016:05:13:03
18JUL2016:05:28:40
18JUL2016:05:44:15
18JUL2016:05:59:53
18JUL2016:06:15:28
18JUL2016:06:31:07
18JUL2016:06:46:42
18JUL2016:07:02:17
18JUL2016:07:17:55
18JUL2016:07:33:31
18JUL2016:07:49:14
18JUL2016:08:04:51
18JUL2016:08:20:45
18JUL2016:08:36:27
18JUL2016:08:52:05
;
run;
data intermediate;
set have;
date = datepart(datetimevalue);
format date date9.;
hour = hour(datetimevalue);
run;
data want;
set intermediate;
by date hour;
if last.hour;
run;
proc print noobs;
run;
produces this result:
datetimevalue date hour 18JUL2016:03:54:51 18JUL2016 3 18JUL2016:04:57:20 18JUL2016 4 18JUL2016:05:59:53 18JUL2016 5 18JUL2016:06:46:42 18JUL2016 6 18JUL2016:07:49:14 18JUL2016 7 18JUL2016:08:52:05 18JUL2016 8
If you get something different, show code and log, please.
It works!
Thank you very much @Kurt_Bremser
Hi
An example creating a new datetime value, with just the date and the hour, then use SQL for grouping
data have;
infile cards;
input
someDT anydtdtm.
;
groupValue = dhms(datepart(someDT), hour(timepart(someDT)), 0 , 0);
format
someDT datetime22.3
groupValue datetime22.3
;
cards;
18JUL2016:03:08:01
18JUL2016:03:23:40
18JUL2016:03:39:16
18JUL2016:03:54:51
18JUL2016:04:10:30
18JUL2016:04:26:08
18JUL2016:04:41:43
18JUL2016:04:57:20
18JUL2016:05:13:03
18JUL2016:05:28:40
18JUL2016:05:44:15
18JUL2016:05:59:53
18JUL2016:06:15:28
18JUL2016:06:31:07
18JUL2016:06:46:42
18JUL2016:07:02:17
18JUL2016:07:17:55
18JUL2016:07:33:31
18JUL2016:07:49:14
18JUL2016:08:04:51
18JUL2016:08:20:45
18JUL2016:08:36:27
18JUL2016:08:52:05
;
Proc sql;
create table want as
select
groupValue
, timePart(max(someDT)) as maxSomeTime format=time8.
from
have
group by
groupValue
;
quit;
Bruno
data have; input datetimevalue datetime19.; format datetimevalue datetime19.; cards; 18JUL2016:03:08:01 18JUL2016:03:23:40 18JUL2016:03:39:16 18JUL2016:03:54:51 18JUL2016:04:10:30 18JUL2016:04:26:08 18JUL2016:04:41:43 18JUL2016:04:57:20 18JUL2016:05:13:03 18JUL2016:05:28:40 18JUL2016:05:44:15 18JUL2016:05:59:53 18JUL2016:06:15:28 18JUL2016:06:31:07 18JUL2016:06:46:42 18JUL2016:07:02:17 18JUL2016:07:17:55 18JUL2016:07:33:31 18JUL2016:07:49:14 18JUL2016:08:04:51 18JUL2016:08:20:45 18JUL2016:08:36:27 18JUL2016:08:52:05 ; run; proc sql; select * from have group by datepart(datetimevalue),hour(datetimevalue) having datetimevalue=max(datetimevalue); quit;
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.