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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User
data intermediate;
set have;
date = datepart(datetimevalue);
hour = hour(datetimevalue);
run:

data want;
set intermediate;
by date hour;
if last.hour;
run;
jei
Quartz | Level 8 jei
Quartz | Level 8

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.

Reeza
Super User

If your data is sorted correctly the last record per hour should be the largest. 

 

Please explain how this is not working. 

Kurt_Bremser
Super User

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.

jei
Quartz | Level 8 jei
Quartz | Level 8

It works!

Thank you very much @Kurt_Bremser Smiley Happy

BrunoMueller
SAS Super FREQ

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

Ksharp
Super User

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;

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
  • 7 replies
  • 1044 views
  • 3 likes
  • 5 in conversation