BookmarkSubscribeRSS Feed
iank131
Quartz | Level 8

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.

obsdealnumcusip6datewt_Nwtvar10ret10
1167353404545920020/07/200520.39428076.3719E-050.001942508
2167353404545920020/07/200520.60571930.0001680920.002905392
3167353404545920026/07/2005116.3719E-050.000542947
4167475904534537015/06/200570.01765260.000496162-0.01220406
5167475904534537015/06/200570.0463260.0003844260.001794011
6167475904534537015/06/200570.08300540.000516729-0.03776804
7167475904534537015/06/200570.11764750.000297142-0.0457237
8167475904534537015/06/200570.12035260.000277292-0.02096441
9167475904534537015/06/200570.20621270.000157504-0.01184258
10167475904534537015/06/200570.4088037.55415E-05-0.01552909
11167475904534537016/06/200570.01746360.000496162-0.02186809
12167475904534537016/06/200570.04544440.000384426-0.02943547
13167475904534537016/06/200570.08555120.0005167290.018480605
14167475904534537016/06/200570.12043310.0002971420.011245789
15167475904534537016/06/200570.12104260.000277292-0.006116
16167475904534537016/06/200570.20627870.000157504-0.01120836
17167475904534537016/06/200570.40378647.55415E-05-0.02312268
18167559504545920018/05/200520.39834575.96785E-05-0.00159973
19167559504545920018/05/200520.60165430.000176524-0.00643742
20167559504545920019/05/2005115.96785E-05-0.00917604
21167559504545920023/05/200520.39937925.96785E-050.018426452
22167559504545920023/05/200520.60062080.0001765240.006719993

Many thanks in advance,

Ian.

2 REPLIES 2
Rick_SAS
SAS Super FREQ

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.

iank131
Quartz | Level 8

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 2 replies
  • 1912 views
  • 4 likes
  • 2 in conversation