BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Prat009
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Srikanth_B
Calcite | Level 5

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;

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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
Opal | Level 21

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
Fluorite | Level 6
Thanks for your response!
Srikanth_B
Calcite | Level 5

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
Fluorite | Level 6
Thanks a lot ! Both of your solutions worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 5245 views
  • 2 likes
  • 4 in conversation