turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Weighted average of a var in a BY group with proc ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2015 09:45 AM

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.

Accepted Solutions

Solution

03-14-2015
10:20 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to iank131

03-14-2015 10:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to iank131

03-14-2015 10:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

03-14-2015 08:10 PM

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.