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

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-06-2012 07:44 AM

Hi,

I think my problem is quite easy for someone who knows PROC IML, so I believe this is the right discussion forum. If someone could suggest a solution, I would be very appreciative. I am familiar with base SAS and its database programming aspects, but I am new to PROC IML and matrix/ vector multiplication.

My problem is this: Given the returns, variances, and weights of n individual assets, I want to combine these assets into a single weighted-average portfolio and then calculate the variance and return of the portfolio. The problem is I have thousands of portfolios to create and each portfolio has a different number of assets, many with only 1 or 2 assets, others with 10 assets. The portfolio variance calculation changes depending on n. Using normal base SAS and linear combinations makes the problem complicated, but using matrix/vector multiplication makes the solution to the problem very easy and flexible since:

E(Rp) = w' * R where E(Rp) = expected return on the portfolio; R is the column vector of asset returns; w is the col vector of weights and w' is the transpose of this vector

Var(Rp) = w' * ( V * V' ) * w where V(Rp) = the variance of the portfolio; V is col vector of asset variances and V' is the transpose.

(NOTE: I am assuming a correlation coeffecient of 1 between the returns on all asset)

My data to construct these vectors comes in the following form: I have three BY variables (dealnum, cusip6, date), I know the number of assets in each portfolio (wt_N), I am given the weight of each asset in the portfolio (wt -- the weights of the portfolio add up to 1), I have variance for the same asset (var10) and the return for this asset (ret10). So referring to the data below, Porfolio 1 is consists of Obs 1, 2; Portfolio 2 is obs 3 only; Portfolio 3 is of obs 4 to 17; Portfolio 4 is of obs 18, 19; Porfolio 5 is of obs 20; and Porfolio 6 is of obs 21, 22, etc (up to n=4000 portfolios). For each of the these portfolios the vectors w, R and V are different, but using matrix/vector multiplication should make this problem REALLY EASY!! :smileygrin:

Could someone show me how this could be done in PROC IML (or otherwise)? I attach a more complete sample of data in case you want it.

obs | dealnum | cusip6 | date | wt_N | wt | var10 | ret10 |

1 | 1673534045 | 459200 | 20/07/2005 | 2 | 0.3942807 | 6.3719E-05 | 0.001942508 |

2 | 1673534045 | 459200 | 20/07/2005 | 2 | 0.6057193 | 0.000168092 | 0.002905392 |

3 | 1673534045 | 459200 | 26/07/2005 | 1 | 1 | 6.3719E-05 | 0.000542947 |

4 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.0176526 | 0.000496162 | -0.01220406 |

5 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.046326 | 0.000384426 | 0.001794011 |

6 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.0830054 | 0.000516729 | -0.03776804 |

7 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.1176475 | 0.000297142 | -0.0457237 |

8 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.1203526 | 0.000277292 | -0.02096441 |

9 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.2062127 | 0.000157504 | -0.01184258 |

10 | 1674759045 | 345370 | 15/06/2005 | 7 | 0.408803 | 7.55415E-05 | -0.01552909 |

11 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.0174636 | 0.000496162 | -0.02186809 |

12 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.0454444 | 0.000384426 | -0.02943547 |

13 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.0855512 | 0.000516729 | 0.018480605 |

14 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.1204331 | 0.000297142 | 0.011245789 |

15 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.1210426 | 0.000277292 | -0.006116 |

16 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.2062787 | 0.000157504 | -0.01120836 |

17 | 1674759045 | 345370 | 16/06/2005 | 7 | 0.4037864 | 7.55415E-05 | -0.02312268 |

18 | 1675595045 | 459200 | 18/05/2005 | 2 | 0.3983457 | 5.96785E-05 | -0.00159973 |

19 | 1675595045 | 459200 | 18/05/2005 | 2 | 0.6016543 | 0.000176524 | -0.00643742 |

20 | 1675595045 | 459200 | 19/05/2005 | 1 | 1 | 5.96785E-05 | -0.00917604 |

21 | 1675595045 | 459200 | 23/05/2005 | 2 | 0.3993792 | 5.96785E-05 | 0.018426452 |

22 | 1675595045 | 459200 | 23/05/2005 | 2 | 0.6006208 | 0.000176524 | 0.006719993 |

Many thanks in advance,

Ian.

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

Posted in reply to iank131

11-06-2012 11:10 AM

Here are a few tips.

1) Read about the UNIQUE-LOC technique, to get the main ideas of how to do something like this The UNIQUE-LOC trick: A real treat! - The DO Loop

2) Because you have three BY-group variables, I recommend implementing the efficiency scheme here: An efficient alternative to the UNIQUE-LOC technique - The DO Loop

3) If the data are too large to fit into memory, use a variation on this approach BY-group processing in SAS/IML - The DO Loop

4) Develop and debug on a small set of sample data. When it is working, scale up to the real data.

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

Posted in reply to Rick_SAS

11-08-2012 04:27 AM

Hi Rick,

Thanks very much for your very helpful tips. They helped me focus my learning of PROC IML on my concrete problem, which has now been resolved thanks to the tips you gave. Reducing my three BY variables to just one by pre-sorting the data set before PROC IML helped resolve many problems.

Thanks again! I really appreciate your help.

Regards

Ian.