PROC OPTMODEL how to read SAS data into num matrix

[tinazhang]

Hi I am reading the OPTMODEL document from the help center.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/ormpug/ormpug_qpsolver_examples02.htm

/* example 2: portfolio optimization */
proc optmodel;
   /* let x1, x2, x3, x4 be the amount invested in each asset */
   var x{1..4} >= 0;

   num coeff{1..4, 1..4} = [0.08 -.05 -.05 -.05
                            -.05 0.16 -.02 -.02
                            -.05 -.02 0.35 0.06
                            -.05 -.02 0.06 0.35];
   num r{1..4}=[0.05 -.20 0.15 0.30];

   /* minimize the variance of the portfolio's total return */
   minimize f = sum{i in 1..4, j in 1..4}coeff[i,j]*x[i]*x[j];

   /* subject to the following constraints */
   con BUDGET: sum{i in 1..4}x[i] <= 10000;
   con GROWTH: sum{i in 1..4}r[i]*x[i] >= 1000;

   solve with qp;

   /* print the optimal solution */
   print x;

In the example of portfolio optimization, the user needs to manually change the matrix (coeff and r). I have the coefficient (covariance) and r(mean) from previous steps

proc corr noprob outp=OutCorr /** store results **/
nomiss
cov; /** include covariance **/
var ret1 ret2 ret3 ret4;
run;

The mean dataset is as the following-I only list 1*2 matrix for the illustration:

 ret1 .. ret4

0.2      0.5

The variance dataset is as the following-I only list the 2*2 matrix for the illustration

ret1 ret2

0.3  0.4

0.4  0.5

My question is:

How can I read the datasets from PROC CORRELATION to the PROC OPTMODEL's num matrix coeff{1..4, 1..4} and r{1..4}.

Thank you very much.

[sbxkoenk]

Hello,

I have moved your post to the "SAS Optimization (OR) and Simulation" - board !

Use the READ DATA statement to read parameters from a SAS data set.

SAS® 9.4 and SAS® Viya® 3.5 Programming Documentation | SAS 9.4 / Viya 3.5
SAS/OR User's Guide: Mathematical Programming
The OPTMODEL Procedure
Example 5.2 Reading From and Creating a Data Set
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/ormpug/ormpug_optmodel_examples02.htm

Koen

[tinazhang]

Thank you Koen.

[ChanceTGardener · Accepted solution]

Below is one approach for reading the OUTCORR table directly into the OPTMODEL procedure. The example uses the  SASHELP.BASEBALL data set to generate the covariances and mean vector. 

By creating a set called ASSETS, the OPTMODEL code below will accommodate as many assets as you specify in the VAR statement within PROC CORR, so you no longer need to hard key 1..4. 

proc corr data=sashelp.baseball noprob outp=OutCorr /** store results **/
nomiss
cov; /** include covariance **/
var nAtBat nHits nRBI nBB;
run;

proc optmodel;

 num dsid = open('OutCorr');
 num ncol = attrn(dsid,'nvar');

 set ASSETS = setof {j in 3..ncol} varname(dsid,j);

 num coeff{ASSETS,ASSETS};

 num r{ASSETS};

 read data OutCorr(where=(_TYPE_='COV')) into [_NAME_]
  {j in ASSETS} <coeff[_NAME_,j] = col(j)>;

 read data OutCorr(where=(_TYPE_='MEAN')) into [_NAME_] 
   {j in ASSETS} <r[j] = col(j)>;

 print coeff;
 print r;

 /* let x1, x2, x3, x4 be the amount invested in each asset */
 var x{ASSETS} >= 0;

 /* minimize the variance of the portfolio's total return */
 minimize f = sum{i in ASSETS, j in ASSETS} coeff[i,j]*x[i]*x[j];

 /* subject to the following constraints */
 con BUDGET: sum{i in ASSETS} x[i] <= 10000;
 con GROWTH: sum{i in ASSETS} r[i]*x[i] >= 1000;

 solve with qp;

 /* print the optimal solution */
 print x;
quit;

[tinazhang]

Thank you. That's what I need.