Solved
Contributor
Posts: 74

# Getting the Maximum

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!

Accepted Solutions
Solution
‎07-25-2016 09:58 PM
Super User
Posts: 10,279

## Re: Getting the Maximum

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 10,279

## Re: Getting the Maximum

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

data want;
set intermediate;
by date hour;
if last.hour;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 74

## Re: Getting the Maximum

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.

Super User
Posts: 23,771

## Re: Getting the Maximum

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

Please explain how this is not working.

Solution
‎07-25-2016 09:58 PM
Super User
Posts: 10,279

## Re: Getting the Maximum

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 74

## Re: Getting the Maximum

It works!

Thank you very much @KurtBremser

SAS Super FREQ
Posts: 825

## Re: Getting the Maximum

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

Super User
Posts: 10,787

## Re: Getting the Maximum

```
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;

```
☑ This topic is solved.