BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## PROC OPTMODEL how to read SAS data into num matrix

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Employee

## Re: PROC OPTMODEL how to read SAS data into num matrix

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;``````
4 REPLIES 4
SAS Super FREQ

## Re: PROC OPTMODEL how to read SAS data into num matrix

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

Fluorite | Level 6

Thank you Koen.
SAS Employee

## Re: PROC OPTMODEL how to read SAS data into num matrix

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;``````
Fluorite | Level 6

## Re: PROC OPTMODEL how to read SAS data into num matrix

Thank you. That's what I need.
Discussion stats
• 4 replies
• 459 views
• 6 likes
• 3 in conversation