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

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 CompanyClass of StocksDateStock Price
XXXA10/20/14100
XXXA9/20/14110
XXXA8/20/14105
XXXB10/21/14120
XXXB9/21/14122
XXXB8/22/14124
XXXB11/21/14127
XXXC10/20/14130
XXXC9/20/14133
XXXC8/20/14137
YYYYYA10/20/14102
YYYYYA9/20/14108
YYYYYA8/20/14104
YYYYYB10/21/14115
YYYYYB9/21/14113
YYYYYB8/22/14110
YYYYYB11/21/14118
YYYYYC10/20/14121
YYYYYC9/20/14126
YYYYYC8/20/14122

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 CompanyClass of StocksDateStock PriceAverageprice
XXXA10/20/14100105
XXXA9/20/14110105
XXXA8/20/14105105
XXXB10/21/14120123.25
XXXB9/21/14122123.25
XXXB8/22/14124123.25
XXXB11/21/14127123.25
XXXC10/20/14130133
XXXC9/20/14133133
XXXC8/20/14136133
YYYYYA10/20/14102104.6666667
YYYYYA9/20/14108104.6666667
YYYYYA8/20/14104104.6666667
YYYYYB10/21/14115114
YYYYYB9/21/14113114
YYYYYB8/22/14110114
YYYYYB11/21/14118114
YYYYYC10/20/14121123
YYYYYC9/20/14126123
YYYYYC8/20/14122123

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.

1 ACCEPTED SOLUTION

Accepted Solutions
gergely_batho
SAS Employee

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;

View solution in original post

4 REPLIES 4
gergely_batho
SAS Employee

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;

m1986MM
Obsidian | Level 7

How can I eliminate the repetition in data? For example if I want to find the following table:

Name of CompanyClass of StocksAverageprice
XXXA105
XXXB123.25
XXXC133
YYYYYA104.6666667
YYYYYB114
YYYYYC123
gergely_batho
SAS Employee

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.

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!

What is ANOVA?

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.

Discussion stats
  • 4 replies
  • 1461 views
  • 4 likes
  • 2 in conversation