Solved
Contributor
Posts: 53

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

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)DatePriceWeights (trdsize)
A110100
A111200
A112300
A111350
B130300
B132400
B234100

What I want is this:

A110*100/(100+200+300+350) + 11*200/950 + 12*300/950 + 11*350/950
B130*300/(300+400) + 32*400/700
B234*100/100

I attach a data file with a more comprehensive sample.

Accepted Solutions
Solution
‎03-14-2015 10:20 AM
Super User
Posts: 10,787

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

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

All Replies
Solution
‎03-14-2015 10:20 AM
Super User
Posts: 10,787

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

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

Contributor
Posts: 53

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

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.

🔒 This topic is solved and locked.