10282015 11:17 PM  edited 10282015 11:18 PM
Hi,
suppose that I have the following stock return data for 3 stocks:
data returns;
input abc def zzz;
datalines;
10 2 5
3 5 7
1 7 6
1 3 2
;
run;
I used the following code to calculate the efficient frontier, i.e, for each level of risk (variance) to find the optimal return and the weights of the stocks that correspond to each riskreturn combination:
Proc IML;
use returns;
read all var _num_ into RMAT[colname=varNames];
Print RMAT;
f = 1;
g = 2;
n = 4; /* number of rows*/
k = 3; /* number of columns*/
RAVG = j(f,k,0);
RAVG=RMAT[:,]; /* to calculate column average returns, a 1 x k matrix*/
Print RAVG;
xbar = REPEAT(RAVG,n,f); /* to create an n x k matrix*/
Print xbar;
RMX = j(n,k,0);
RMX = RMATxbar; /* to calculate RMX*/
Print RMX;
V = j(k,k,0);
V=RMX`*RMX /(n1); /* to calculate covariance matrix*/
Print V;
VINV =j(k,k,0);
VINV = INV(V); /* to take inverse of V*/
Print VINV;
P = 0.05;
M={1,.05}; /* to define M and required return (must change)*/
Print M;
DIG = j(1,k,1);
R = j(g,k,0);
R =DIG//RAVG; /* to concatenate (combine) DIG and RAVG to a 2 x k matrix*/
Print R;
H =j(2,2,0);
H = R*inv(V)*R`; /* to calculate H*/
Print H;
HINV = j(2,2,0);
HINV = INV(H); /* to take inverse of H*/
PRINT HINV;
Do until (P<=0);
W = j(1,k,0);
W = VINV*R`*HINV*M; /* to calculate portfolio weights*/
Print W;
PVAR = j(1,1,0);
PVAR = W`*V*W; /* to calculate portfolio variance given a return and weights*/
Print PVAR;
P = P.005;
M = M  {0, .005};
end;
quit;
Basically the core of the code is done, what I need are 2 special additions:
1) I would like to create a table which will contain the information in the following way:
Weight abc 
Abc1 
Abc2 
Etc. 
Weight def 
Def1 
Def2 

Weight zzz 
Zzz1 
Zzz2 

return 
Ret1 
Ret2 

variance 
Var1 
Var2 

2) I would like to add the constraint of no short selling, i.e, the weights should all be greater or equal to 0. I just don't know how to add constraints into Proc IML
Thank you very much!!
10292015 04:30 AM  edited 10292015 04:34 AM
I don't have a complete understanding of what you are trying to do here, but I think I can answer your first question. You need to create a matrix large enough to hold all the results and then use a do loop with a counter. Then as the loop iterates, write submatrices of the results table.
rtab = j(k+1, 10);
do i = 1 to 10;
<existing code from do loop here>
rtab[1:k, i] = W;
rtab[k+1, i] = PVAR;
end;
print rtab [rowname=varNames];
A few other comments. V = COV(RMAT) will give the covariance matrix in just one statement, and note that there is no need to write declarative statements like V = j(k, k, 0);
10292015 01:37 PM
And here are some general programming tips to simplify your code:
1) Use the NROW and NCOL functions to get the number of rows and columns for RMAT
2) Use the MEAN and COV functions for the computations of RAVG and V.
3) You don't need to use the REPEAT statement to make the mean vector a full matrix. SAS/IML knows how to subtract a mean vector from each row of a matrix.
4) There is no need to "preallocate" every variable before you assign to it. Just make the assignment and the dimensions of the variable will be determined automatically.
5) [Optional] You can get rid of the final loop if you want. You can create a 2 x 10 matrix of all the values of W and use that matrix in the computations.
The simplified code looks like this:
Proc IML;
use returns;
read all var _num_ into RMAT[colname=varNames];
n = nrow(RMAT); /* number of rows*/
k = ncol(RMAT); /* number of columns*/
RAVG = mean(RMAT); /* mean of columns */
V = cov(RMAT); /* covariance matrix */
VINV = INV(V); /* inverse of V */
R = j(1,k,1) // RAVG; /* concatenate DIG and RAVG to a 2 x k matrix*/
H = R*VINV*R`;
P = do(0.05, 0.005, 0.005); /* sequence of values for P */
M = j(1, ncol(P), 1) // P;
W = VINV*R`*inv(H)*M; /* portfolio weights*/
PVAR = T( vecdiag(W`*V*W )); /* portfolio variance given a return and weights*/
print W[colname=(char(P)) rowname=varNames],
PVAR[colname=(char(P))];
10292015 01:40 PM
For (2), do you mean that you want to replace any negative weights with zeros? If so, you can use the elementwise maximum operator as follows:
W = W <> 0;
10302015 09:31 PM
Hi Rick,
thank you for your code, now I have the weights in W and the variances in PVAR, for any given level of return. But is it possible to cretae data tables for both W and PVAR becasue when I tried doing:
data all;
set w pvar;
run;
I got an error message that tables w and pvar don't exist.
For the second part of the question, I just realized that I forgot to mention that there is another constraint: the sum of all weights should be equal to one  and this constraint is also applicable to the code that I put in the beginning of this topic.
So the additional constraint that each weight is >=0 would affect the entire calculaton from the beginning, otherwise the result would lose its meaning if all the negative weights are made = 0 and all the others kept as they are.
Thank you very much!!!
11012015 07:06 AM
See this article about how to write data from SAS/IML matrices to SAS data sets.
If your goal is to find the optimal solution for a linear problem with constraints, that operation is called "linear programming." See the "Linear programming" example in the SAS/IML documentation. In SAS/IML 13.1 and beyond, use the LPSOLVE call to solve linear programming problems.
11012015 11:58 AM
Hi Rick,
I went to the link for the discussion on your blog and managed to create 2 tables. But now I have 2 small problems with the names of the columns and rows.
Here is my code:
data returns;
input abc def zzz;
datalines;
10 2 5
3 5 7
1 7 6
1 3 2
;
run;
Proc IML;
use returns;
read all var _num_ into RMAT[colname=varNames];
n = nrow(RMAT); /* number of rows*/
k = ncol(RMAT); /* number of columns*/
RAVG = mean(RMAT); /* mean of columns */
V = cov(RMAT); /* covariance matrix */
VINV = INV(V); /* inverse of V */
R = j(1,k,1) // RAVG; /* concatenate DIG and RAVG to a 2 x k matrix*/
H = R*VINV*R`;
P = do(0.05, 0.005, 0.005); /* sequence of values for P */
M = j(1, ncol(P), 1) // P;
W = VINV*R`*inv(H)*M; /* portfolio weights*/
PVAR = T( vecdiag(W`*V*W )); /* portfolio variance given a return and weights*/
print W[colname=(char(P)) rowname=varNames],
PVAR[colname=(char(P)) rowname={'variance'}];
create Weights from W [colname=(char(p)) ] ;
append from W;
close Weights;
create Variance from pvar [colname=(char(P))] ;
append from pvar;
close Variance;
1) the column names for both new tables are output in a strange way. For ex, it should be 0.05 but I get _0D05
2) for the new table Weights instead of getting the stock names on the left I get numbers 1,2,3.
I tried doing
create Weights from W [colname=(char(p)) rowname=varNames ] ;
but then didn't get any table at all and an error message saying that "number of columns in W does not match witht he number of variables in the data set".
Thank you!
11012015 06:05 PM  edited 11012015 06:11 PM
Hi Rick,
concerning my first reply to your last poste I think that I managed to the tables that I wanted with the necessary column and row names:
/*creating tables return and Variance  the vector P contains the returns!!!*/
create ret from p ;
append from p;
close ret;
create variance from pvar ;
append from pvar;
close variance; quit;
/*to put return and variance together*/
/*transposing and renaming*/
proc transpose data=variance
out=var_transposed;run;
data ret_transposed;
set ret_transposed (rename=(col1=return)); run;
/*adding n*/
data var_transposed;
set var_transposed;
n=_n_;run;
/*merging*/
data return_variance;
merge ret_transposed var_transposed;
by n; run;
/*to put all together*/
/*extracting the stock names*/
proc contents data=returns out=stocks (keep=NAME) ;
run ;
proc print data=stocks ; run ;
/*creating the name "variance"*/
data v;
input name$;
datalines;
variance
; run;
data names;
set stocks v;
n=_n_; run;
/*setting the weights and variance*/
data weights_variance;
set weights variance;
n=_n_; run;
/*combining all into a table*/
data swv;
merge weights_variance names;
by n; run;
As for my question about the constraint, is it possible to do an efficient frontier in proc iml with the constraint (becasue linear optimization is a onebyone case)?
Thank you!!!
11122015 10:19 AM
It would have been helpful if you had linked to this paper when you asked your question: "SAS and the Efficient Portfolio by Thomas H. Thompson and Ashraf ElHoubi".
11132015 08:53 PM
Hi Rick,
what a coincidence, I actually also found this paper before posting this question but the paper handles only the unrestricted case (i.e, short selling allowed so that the weights of the stocks can be negative) and that's the reason why I posted this question, in order to do the restricted case when short selling is not allowed.
Thank you!