topic Find max of hourly data for each month and what date and time the max occured in SAS Programming

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

KALLEN — Wed, 10 Oct 2018 14:09:11 GMT

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

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

mkeintz — Tue, 09 Oct 2018 23:09:16 GMT

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.

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

PGStats — Wed, 10 Oct 2018 04:06:54 GMT

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;

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

KALLEN — Wed, 10 Oct 2018 14:10:18 GMT

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