Getting the Maximum

[jei]

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!

[Kurt_Bremser]

data intermediate;
set have;
date = datepart(datetimevalue);
hour = hour(datetimevalue);
run:

data want;
set intermediate;
by date hour;
if last.hour;
run;

[jei]

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]

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

Please explain how this is not working. 

[Kurt_Bremser · Accepted solution]

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]

It works!

Thank you very much @Kurt_Bremser 

[BrunoMueller]

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]

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;