I'm looking to recreate the averageif function from Excel. I have the following columns: Date, Hour, Peak, Price. Peak is boolean (0 or 1). Sample dataset:
Date | HE | Peak | Price | Avg |
1/4/2017 | 1 | 0 | 28.48 | 30.84 |
1/4/2017 | 2 | 0 | 28.15 | 30.84 |
1/4/2017 | 3 | 0 | 27.61 | 30.84 |
1/4/2017 | 4 | 0 | 26.61 | 30.84 |
1/4/2017 | 5 | 0 | 28.14 | 30.84 |
1/4/2017 | 6 | 0 | 36.16 | 30.84 |
1/4/2017 | 7 | 1 | 41.32 | 39.05 |
1/4/2017 | 8 | 1 | 45.40 | 39.05 |
1/4/2017 | 9 | 1 | 39.01 | 39.05 |
1/4/2017 | 10 | 1 | 34.93 | 39.05 |
1/4/2017 | 11 | 1 | 33.62 | 39.05 |
1/4/2017 | 12 | 1 | 31.70 | 39.05 |
1/4/2017 | 13 | 1 | 26.33 | 39.05 |
1/4/2017 | 14 | 1 | 29.54 | 39.05 |
1/4/2017 | 15 | 1 | 32.51 | 39.05 |
1/4/2017 | 16 | 1 | 37.27 | 39.05 |
1/4/2017 | 17 | 1 | 41.96 | 39.05 |
1/4/2017 | 18 | 1 | 51.82 | 39.05 |
1/4/2017 | 19 | 1 | 51.53 | 39.05 |
1/4/2017 | 20 | 1 | 45.52 | 39.05 |
1/4/2017 | 21 | 1 | 42.72 | 39.05 |
1/4/2017 | 22 | 1 | 39.69 | 39.05 |
1/4/2017 | 23 | 0 | 36.53 | 30.84 |
1/4/2017 | 24 | 0 | 35.04 | 30.84 |
I want to find the average (Avg) for each day by Peak type. Some days could have all zeros for Peak. Looking to do this in a data step if possible. Thanks in advance
data have;
input Date :mmddyy10. HE Peak Price;
format date mmddyy10.;
datalines;
1/4/2017 1 0 28.48
1/4/2017 2 0 28.15
1/4/2017 3 0 27.61
1/4/2017 4 0 26.61
1/4/2017 5 0 28.14
1/4/2017 6 0 36.16
1/4/2017 7 1 41.32
1/4/2017 8 1 45.40
1/4/2017 9 1 39.01
1/4/2017 10 1 34.93
1/4/2017 11 1 33.62
1/4/2017 12 1 31.70
1/4/2017 13 1 26.33
1/4/2017 14 1 29.54
1/4/2017 15 1 32.51
1/4/2017 16 1 37.27
1/4/2017 17 1 41.96
1/4/2017 18 1 51.82
1/4/2017 19 1 51.53
1/4/2017 20 1 45.52
1/4/2017 21 1 42.72
1/4/2017 22 1 39.69
1/4/2017 23 0 36.53
1/4/2017 24 0 35.04
;
proc sql;
create table want as
select *,
mean(Price) as Avg
from have
group by Peak
order by HE;
quit;
data have;
input Date :mmddyy10. HE Peak Price;
format date mmddyy10.;
datalines;
1/4/2017 1 0 28.48
1/4/2017 2 0 28.15
1/4/2017 3 0 27.61
1/4/2017 4 0 26.61
1/4/2017 5 0 28.14
1/4/2017 6 0 36.16
1/4/2017 7 1 41.32
1/4/2017 8 1 45.40
1/4/2017 9 1 39.01
1/4/2017 10 1 34.93
1/4/2017 11 1 33.62
1/4/2017 12 1 31.70
1/4/2017 13 1 26.33
1/4/2017 14 1 29.54
1/4/2017 15 1 32.51
1/4/2017 16 1 37.27
1/4/2017 17 1 41.96
1/4/2017 18 1 51.82
1/4/2017 19 1 51.53
1/4/2017 20 1 45.52
1/4/2017 21 1 42.72
1/4/2017 22 1 39.69
1/4/2017 23 0 36.53
1/4/2017 24 0 35.04
;
proc sql;
create table want as
select *,
mean(Price) as Avg
from have
group by Peak
order by HE;
quit;
So much easier in proc sql then forcing myself to do in data step. Thanks for your help! I ended up using this:
proc sql;
select *,
mean(price) as Avg
from temp_new
group by Date, Peak
order by Date, Hour;
quit;
The right tool for the right job 🙂 Glad you found your answer.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: