topic Re: Conditional average in SAS Procedures

Conditional average

diego4444 — Tue, 18 Jun 2019 21:26:27 GMT

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

Re: Conditional average

PeterClemmensen — Tue, 18 Jun 2019 21:34:28 GMT

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;

Re: Conditional average

diego4444 — Tue, 18 Jun 2019 21:56:44 GMT

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;

Re: Conditional average

PeterClemmensen — Tue, 18 Jun 2019 21:42:07 GMT

The right tool for the right job 🙂 Glad you found your answer.