DATA Step, Macro, Functions and more

Getting the Maximum

Accepted Solution Solved
Reply
Contributor jei
Contributor
Posts: 74
Accepted Solution

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: 7,854

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

View solution in original post


All Replies
Super User
Posts: 7,854

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
Contributor jei
Contributor
Posts: 74

Re: Getting the Maximum

Posted in reply to KurtBremser

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: 19,867

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: 7,854

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
Contributor jei
Contributor
Posts: 74

Re: Getting the Maximum

Posted in reply to KurtBremser

It works!

Thank you very much @KurtBremser Smiley Happy

SAS Super FREQ
Posts: 709

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,044

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 338 views
  • 3 likes
  • 5 in conversation