topic Re: An easy question about finding out the mean and adding it to data set as a variable in Statistical Procedures

An easy question about finding out the mean and adding it to data set as a variable

m1986MM — Mon, 27 Oct 2014 12:39:09 GMT

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.

Re: An easy question about finding out the mean and adding it to data set as a variable

gergely_batho — Mon, 27 Oct 2014 12:47:48 GMT

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;

Re: An easy question about finding out the mean and adding it to data set as a variable

m1986MM — Mon, 27 Oct 2014 13:25:02 GMT

Thank you.

Re: An easy question about finding out the mean and adding it to data set as a variable

m1986MM — Mon, 27 Oct 2014 13:42:01 GMT

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

Re: An easy question about finding out the mean and adding it to data set as a variable

gergely_batho — Mon, 27 Oct 2014 13:48:10 GMT

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.