Find max of hourly data for each month and what date and time the max occured

[KALLEN]

How do I find the max of hourly data by month and keep the date and hour that the max values occurred?

proc timeseries data=avg_weighted_kw_class out=max_energy_class;
	var class;
	id date interval=month accumulate=maximum;
run;

Current output looks like this: 

DATE           Class
JAN2018    7.6829856445
FEB2018    7.4240397874
MAR2018   8.6926150969
APR2018   11.188461168
MAY2018   12.989846577
JUN2018    14.024950332

Desired output:

HOUR    DATE        CLASS

14:00     3-Jan-18    7.682985645

17:00     9-Feb-18    7.424039787

16:00    23-Mar-18   8.692615097

16:00    24-Apr-18   11.18846117

15:00   31-May-18   12.98984658

14:00    26-Jun-18    14.02495033

[mkeintz]

I don't think proc timeseries is a good tool to get the data and hour corresponding to the maximum monthly class value. This DATA step is probably the best option:

data want (keep=date hour maxclass);
  set have (keep=date class) end=end_of_have;

  if dif(month(date)) or end_of_have then do;
    if ptr^=. then do;
      set have (keep=date hour class rename=(class=maxclass)) point=ptr;
      output; 
    end;
    call missing(maxclass,ptr);
  end;

  if class>maxclass then do;
    maxclass=class;
    ptr=_n_;
  end;
run;
   

This program assumes at least one incoming observation per month.  The technique here is to keep track of the maxclass value and the observation number (ptr) from which it arises. So at the beginning of the next month (or at end of data) just reread the specified record and output it.

[KALLEN]

Thank you for your solution. Although I am still learning SQL, I appreciated the efficiency of PGStats' answer.

[PGStats · Accepted solution]

With SQL:

proc sql;
create table max_energy_class as
select
    intnx("month", date, 0) as month format=mmyyd7.,
    date,
    hour,
    class
from avg_weighted_kw_class 
group by calculated month
having class = max(class);
quit;