Dear all,
I want to find the mean of different group in my data set and create a variable for the mean. I would appreciate if you suggest a solution to my problem.
Suppose I have the below data set:
Name of Company | Class of Stocks | Date | Stock Price |
XXX | A | 10/20/14 | 100 |
XXX | A | 9/20/14 | 110 |
XXX | A | 8/20/14 | 105 |
XXX | B | 10/21/14 | 120 |
XXX | B | 9/21/14 | 122 |
XXX | B | 8/22/14 | 124 |
XXX | B | 11/21/14 | 127 |
XXX | C | 10/20/14 | 130 |
XXX | C | 9/20/14 | 133 |
XXX | C | 8/20/14 | 137 |
YYYYY | A | 10/20/14 | 102 |
YYYYY | A | 9/20/14 | 108 |
YYYYY | A | 8/20/14 | 104 |
YYYYY | B | 10/21/14 | 115 |
YYYYY | B | 9/21/14 | 113 |
YYYYY | B | 8/22/14 | 110 |
YYYYY | B | 11/21/14 | 118 |
YYYYY | C | 10/20/14 | 121 |
YYYYY | C | 9/20/14 | 126 |
YYYYY | C | 8/20/14 | 122 |
As you see, I have the stock price of different class of stocks for some firms, at different time.
Now I want to find out the average stock price for each class of stocks ( and create a separate variable for the mean amount. In other word, I need to reach to the below table:
Name of Company | Class of Stocks | Date | Stock Price | Averageprice |
XXX | A | 10/20/14 | 100 | 105 |
XXX | A | 9/20/14 | 110 | 105 |
XXX | A | 8/20/14 | 105 | 105 |
XXX | B | 10/21/14 | 120 | 123.25 |
XXX | B | 9/21/14 | 122 | 123.25 |
XXX | B | 8/22/14 | 124 | 123.25 |
XXX | B | 11/21/14 | 127 | 123.25 |
XXX | C | 10/20/14 | 130 | 133 |
XXX | C | 9/20/14 | 133 | 133 |
XXX | C | 8/20/14 | 136 | 133 |
YYYYY | A | 10/20/14 | 102 | 104.6666667 |
YYYYY | A | 9/20/14 | 108 | 104.6666667 |
YYYYY | A | 8/20/14 | 104 | 104.6666667 |
YYYYY | B | 10/21/14 | 115 | 114 |
YYYYY | B | 9/21/14 | 113 | 114 |
YYYYY | B | 8/22/14 | 110 | 114 |
YYYYY | B | 11/21/14 | 118 | 114 |
YYYYY | C | 10/20/14 | 121 | 123 |
YYYYY | C | 9/20/14 | 126 | 123 |
YYYYY | C | 8/20/14 | 122 | 123 |
In table above for different dates of each type of stocks is shown in the averageprice column.
I tried the following code:
proc sql;
create table Average_price as
select *,
mean (Stock price) as Averageprice
from Stock_price;
quit;
However, the result just shows the total average, not the average price for specific class of a certain company's stocks.
Thanks for your help.
proc sql;
create table Average_price as
select *,
mean (Stock price) as Averageprice
from Stock_price
GROUP BY name_of_company, class_of_stocks;
quit;
proc sql;
create table Average_price as
select *,
mean (Stock price) as Averageprice
from Stock_price
GROUP BY name_of_company, class_of_stocks;
quit;
Thank you.
How can I eliminate the repetition in data? For example if I want to find the following table:
Name of Company | Class of Stocks | Averageprice |
XXX | A | 105 |
XXX | B | 123.25 |
XXX | C | 133 |
YYYYY | A | 104.6666667 |
YYYYY | B | 114 |
YYYYY | C | 123 |
proc sql;
create table Average_price as
select name_of_company, class_of_stocks, mean (Stock price) as Averageprice
from Stock_price
GROUP BY name_of_company, class_of_stocks;
quit;
So: simply include grouping and aggregated variables only.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.