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 | 10*100/(100+200+300+350) + 11*200/950 + 12*300/950 + 11*350/950 |
B | 1 | 30*300/(300+400) + 32*400/700 |
B | 2 | 34*100/100 |
I attach a data file with a more comprehensive sample.
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.