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.