How to calculate mean across two variables in SAS?

[Prat009]

I have a data set which contains data on the average price of unleaded regular gasoline (per gallon), whole large eggs (per dozen), and whole milk (per gallon). The variables in this file are year, month, price, and type of commodity.

Year Month  Price   Commodity
2004    1   1.592   Gas
2004    2   1.672   Gas
2005    1   1.766   Gas
2005    2   1.833   Gas
2006    1   2.009   Gas
2006    2   2.041   Gas
2004    1   1.95    Egg
2004    2   1.979   Egg
2005    1   1.97    Egg
2005    2   1.951   Egg
2006    1   2.032   Egg
2006    2   2.21    Egg
2004    1   2.879   Milk
2004    2   2.814   Milk
2005    1   2.786   Milk
2005    2   2.906   Milk
2006    1   3.374   Milk
2006    2   3.574   Milk

Can anyone help me to create a data set that contains the average price per year for each commodity?

I am able to create a data set that contains the average price per year or per commodity, but unable to calculate average price per year for each commodity.

Note: I am using SAS 9.4 version

[novinosrin]

Are you after this?

data have;
input Year Month  Price   Commodity $;
cards;
2004    1   1.592   Gas
2004    2   1.672   Gas
2005    1   1.766   Gas
2005    2   1.833   Gas
2006    1   2.009   Gas
2006    2   2.041   Gas
2004    1   1.95    Egg
2004    2   1.979   Egg
2005    1   1.97    Egg
2005    2   1.951   Egg
2006    1   2.032   Egg
2006    2   2.21    Egg
2004    1   2.879   Milk
2004    2   2.814   Milk
2005    1   2.786   Milk
2005    2   2.906   Milk
2006    1   3.374   Milk
2006    2   3.574   Milk
;
proc sql;
create table want as
select commodity, mean(price) as avg_price
from have
group by commodity;
quit;

[Patrick]

I believe you've asked for "by year and commodity" so @novinosrin SQL needs a tweak.

proc sql;
  create table want as
/*    select *, mean(price) as avg_price*/
    select year, commodity, mean(price) as avg_price
    from have
    group by year, commodity
  ;
quit;

PROC MEANS would be an alternative to PROC SQL for this task.

[Prat009]

Thanks for your response!

[Srikanth_B · Accepted solution]

These are the two methods you can use.

Data one;
input year month price commodity $;
Datalines;
2004 1 1.592 Gas
2004 2 1.672 Gas
2005 1 1.766 Gas
2005 2 1.833 Gas
2006 1 2.009 Gas
2006 2 2.041 Gas
2004 1 1.95 Egg
2004 2 1.979 Egg
2005 1 1.97 Egg
2005 2 1.951 Egg
2006 1 2.032 Egg
2006 2 2.21 Egg
2004 1 2.879 Milk
2004 2 2.814 Milk
2005 1 2.786 Milk
2005 2 2.906 Milk
2006 1 3.374 Milk
2006 2 3.574 Milk
;
run;

Using Data Step:

proc sort data=one;
by year commodity;
run;

Proc means data=one noprint;
var price;
by year commodity;
output out=two(drop=_type_ _freq_) mean=;
run; 

Using Proc Sql:

proc sql;
create table three as
select year, commodity, mean(price) as mean_price
from one
group by year,commodity;
quit;

[Prat009]

Thanks a lot ! Both of your solutions worked.