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.
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.