topic Re: Weighted average of a var in a BY group with proc expand?? in SAS Procedures

Weighted average of a var in a BY group with proc expand??

iank131 — Sat, 14 Mar 2015 13:45:18 GMT

I am trying to calculate the daily trade-weighted average of stock prices. That is, I want one daily price per stock which is a calculated as the weighted average price based on the traded volume on each day. I have many stocks and many trades each day per stock. I am able to do this using a combination of data steps and proc means, and even with proc SQL (from ideas seen in another post), but is there are more elegant way of doing it? :smileyconfused:

I tried PROC EXPAND, but since I have a different number of obs in each BY group, I am not sure it is possible. It would be great if it could :smileycool:. For example, my data looks something like the following, and my BY group would be CUSIP DATE, my analysis variable would be PRICE, and my WEIGHTS are trdsize:

Stock (CUSIP)	Date	Price	Weights (trdsize)
A	1	10	100
A	1	11	200
A	1	12	300
A	1	11	350
B	1	30	300
B	1	32	400
B	2	34	100

What I want is this:

CUSIP	Date	trade_wght_price
A	1	10100/(100+200+300+350) + 11200/950 + 12300/950 + 11350/950
B	1	30300/(300+400) + 32400/700
B	2	34*100/100

I attach a data file with a more comprehensive sample.

Re: Weighted average of a var in a BY group with proc expand??

Ksharp — Sat, 14 Mar 2015 14:20:51 GMT

I am a little surprise . You can't get it by proc means ?

data have;

input stock $ date price weights;

cards;

A 1 10 100

A 1 11 200

A 1 12 300

A 1 11 350

B 1 30 300

B 1 32 400

B 2 34 100

;

run;

proc summary data=have;

by stock date;

var price;

weight weights;

output out=want(drop=_:) mean=weight_price;

run;

Xia Keshan

Re: Weighted average of a var in a BY group with proc expand??

iank131 — Sun, 15 Mar 2015 00:10:11 GMT

Thanks very much. I learned something about PROC SUMMARY.

As I said I tried it with proc means, but I didn't really understand the examples that were given using the weight option. You helped me clarify it, and elegantly too

Ian.