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
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.