Optimization Action Set
BY-Group Processing
This section contains PROC CAS code.
Note: Input data must be accessible in your CAS session, either as one or more CAS tables or as one or more transient-scope tables. A CAS table has a two-level name: the first level is your CAS engine libref, and the second level is the table name. You refer to this table in the CAS procedure by specifying only the second level. For more information about two-level names, see Chapter 3, Shared Concepts (SAS Optimization: Mathematical Optimization Procedures). A transient-scope table is called directly from the action and exists in memory for the duration of the action. For more information about accessing data, see SAS Viya: System Programming Guide. For more information about PROC CAS and programming in CASL, see SAS Cloud Analytic Services: CASL Programmer’s Guide and SAS Cloud Analytic Services: CASL Reference.
This example illustrates how to use the runOptmodel action with BY-group processing. It uses the NLP solver to solve a MINLP portfolio optimization problem by enumerating subsets of the assets to optimize. You can use the following statements to define the asset data:
/* expected return for assets */
data mycas.means;
input return asset;
datalines;
0.000065763 1
0.002604107 2
0.000753611 3
0.000082178 4
-.000380250 5
0.000399003 6
0.002421262 7
0.002412429 8
0.000773394 9
0.000403047 10
0.001361435 11
;
/* asset return covariance */
data mycas.covdata;
input cov asset1 asset2;
datalines;
0.001353751 1 1
0.000468578 1 2
0.001280990 1 3
-.000032003 1 4
0.000731890 1 5
0.000660113 1 6
0.000641176 1 7
0.000466734 1 8
0.000683927 1 9
0.000658789 1 10
0.000606616 1 11
0.000307035 2 2
0.000534959 2 3
-.000006434 2 4
0.000310362 2 5
0.000304408 2 6
0.000348148 2 7
0.000243561 2 8
0.000311989 2 9
0.000301229 2 10
0.000282500 2 11
0.001448759 3 3
-.000031943 3 4
0.000772008 3 5
0.000745441 3 6
0.000782744 3 7
0.000554453 3 8
0.000762838 3 9
0.000745559 3 10
0.000719599 3 11
0.000026314 4 4
-.000027629 4 5
-.000025069 4 6
-.000016775 4 7
-.000016202 4 8
-.000026060 4 9
-.000026031 4 10
-.000019917 4 11
0.000510235 5 5
0.000463580 5 6
0.000471570 5 7
0.000346507 5 8
0.000479810 5 9
0.000463526 5 10
0.000424889 5 11
0.000455959 6 6
0.000471924 6 7
0.000332697 6 8
0.000462159 6 9
0.000455080 6 10
0.000421059 6 11
0.000572171 7 7
0.000371153 7 8
0.000473532 7 9
0.000470686 7 10
0.000449428 7 11
0.000316483 8 8
0.000337295 8 9
0.000331312 8 10
0.000328404 8 11
0.000482038 9 9
0.000462024 9 10
0.000429875 9 11
0.000456097 10 10
0.000420613 10 11
0.000460050 11 11
;
Each nonempty subset of the 11 assets is checked, for a total of 
trials. The asset information is independent of the trial, so another data table is needed to specify the trial identifier as the BY variable:
%let num_assets=11;
data mycas.trials(partition=(trial) orderby=(trial));
do trial=1 to 2**&num_assets-1;
output;
end;
run;
The DATA step specifies the BY variable list with the PARTITION= and ORDERBY= options for the mycas.trials data table. These options create a CAS table that is both grouped (partitioned) and ordered by the BY variables. For efficiency, input tables for the runOptmodel action with BY-group data should have this organization. Otherwise, the action must copy the input into reorganized temporary tables each time the action is invoked. The temporary tables require extra creation time and storage space.
You can use the following statements to solve the problem for each trial:
option nonotes;
proc cas;
source pgm;
/* assets and related parameters */
set ASSETS;
num return {ASSETS};
num cov {ASSETS, ASSETS} init 0;
read data means nogroupby into ASSETS=[asset] return;
read data covdata nogroupby into [asset1 asset2] cov cov[asset2,asset1]=cov;
num riskLimit init 0.00025;
num minThreshold init 0.1;
/* get the trial identifier for this BY group */
num trial;
read data trials into trial;
/* determine included assets from the binary expansion of trial */
string trial_bits = putn(trial, 'binary'||card(assets)||'.');
set ASSETS_THIS = {i in ASSETS : substr(trial_bits,i,1) ne '0'};
/* declare NLP problem for fixed set of assets */
var AssetPropVar {ASSETS} >= minThreshold <= 1;
max ExpectedReturn = sum {i in ASSETS} return[i] * AssetPropVar[i];
con RiskBound:
sum {i in ASSETS_THIS, j in ASSETS_THIS}
cov[i,j] * AssetPropVar[i] * AssetPropVar[j] <= riskLimit;
con TotalPortfolio:
sum {asset in ASSETS} AssetPropVar[asset] = 1;
put trial=;
put ASSETS_THIS=;
for {i in ASSETS diff ASSETS_THIS}
fix AssetPropVar[i] = 0;
solve with NLP / logfreq=0;
put _solution_status_=;
if _solution_status_ in {'OPTIMAL', 'BEST_FEASIBLE'} then do;
create data results from _solution_status_ ExpectedReturn;
create data solutions from [asset] AssetPropVar;
end;
endsource;
optimization.runOptmodel / code=pgm printlevel=0 groupBy="trial";
run;
option notes;
quit;
The following code finds and displays the best result:
proc cas;
fedsql.execDirect /
query = "create table best {options replace=true} as
select * from results join solutions using(trial)
where ExpectedReturn = (select max(ExpectedReturn) from results)";
table.fetch / table="best";
run;
quit;
The selected trial is shown in Output 2.5.1.
Output 2.5.1: Best Portfolio Result
| Selected Rows from Table BEST | |||||
|---|---|---|---|---|---|
| _Index_ | TRIAL | _SOLUTION_STATUS_ | ExpectedReturn | asset | AssetPropVar |
| 1 | 640 | OPTIMAL | 0.0023518488 | 1 | 0 |
| 2 | 640 | OPTIMAL | 0.0023518488 | 2 | 0.8999741202 |
| 3 | 640 | OPTIMAL | 0.0023518488 | 3 | 0 |
| 4 | 640 | OPTIMAL | 0.0023518488 | 4 | 0.1000258798 |
| 5 | 640 | OPTIMAL | 0.0023518488 | 5 | 0 |
| 6 | 640 | OPTIMAL | 0.0023518488 | 6 | 0 |
| 7 | 640 | OPTIMAL | 0.0023518488 | 7 | 0 |
| 8 | 640 | OPTIMAL | 0.0023518488 | 8 | 0 |
| 9 | 640 | OPTIMAL | 0.0023518488 | 9 | 0 |
| 10 | 640 | OPTIMAL | 0.0023518488 | 10 | 0 |
| 11 | 640 | OPTIMAL | 0.0023518488 | 11 | 0 |
BY-Group Processing
This section contains Lua code for the analysis in the CASL version of this example, which contains details about the results.
Note: In order to run this code, the data that are described in the CASL version need to be accessible to the CAS server. One way to do this is to convert the means data to the comma-separated-value (CSV) file means.csv, convert the covdata data to the CSV file covdata.csv, and then use the following code to load the CSV files into CAS:
s:loadtable{casLib="casuser", path="means.csv"}
s:loadtable{casLib="casuser", path="covdata.csv"}
For more information about coding in Lua, see Getting Started with SAS Viya for Lua and SAS Viya: System Programming Guide.
This example illustrates how to use the runOptmodel action with BY-group processing. It uses the NLP solver to solve a MINLP portfolio optimization problem by enumerating subsets of the assets to optimize.
s:datastep_runCode{
code=[[
data trials(partition=(trial) orderby=(trial));
do trial=1 to 2047;
output;
end;
run;
]], single="yes" }
s:sessionProp_setSessOpt{messageLevel="warning"}
s:optimization_runOptmodel{
code=[[
/* assets and related parameters */
set ASSETS;
num return {ASSETS};
num cov {ASSETS, ASSETS} init 0;
read data means nogroupby into ASSETS=[asset] return;
read data covdata nogroupby into [asset1 asset2] cov cov[asset2,asset1]=cov;
num riskLimit init 0.00025;
num minThreshold init 0.1;
/* get the trial identifier for this BY group */
num trial;
read data trials into trial;
/* determine included assets from the binary expansion of trial */
string trial_bits = putn(trial, 'binary'||card(assets)||'.');
set ASSETS_THIS = {i in ASSETS : substr(trial_bits,i,1) ne '0'};
/* declare NLP problem for fixed set of assets */
var AssetPropVar {ASSETS} >= minThreshold <= 1;
max ExpectedReturn = sum {i in ASSETS} return[i] * AssetPropVar[i];
con RiskBound:
sum {i in ASSETS_THIS, j in ASSETS_THIS}
cov[i,j] * AssetPropVar[i] * AssetPropVar[j] <= riskLimit;
con TotalPortfolio:
sum {asset in ASSETS} AssetPropVar[asset] = 1;
put trial=;
put ASSETS_THIS=;
for {i in ASSETS diff ASSETS_THIS}
fix AssetPropVar[i] = 0;
solve with NLP / logfreq=0;
put _solution_status_=;
if _solution_status_ in {'OPTIMAL', 'BEST_FEASIBLE'} then do;
create data results from _solution_status_ ExpectedReturn;
create data solutions from [asset] AssetPropVar;
end;
]], printlevel=0, groupBy="trial" }
s:sessionProp_setSessOpt{messageLevel="default"}
s:fedsql_execDirect{
query = [[
create table best {options replace=true} as
select * from results join solutions using(trial)
where ExpectedReturn = (select max(ExpectedReturn) from results)
]] }
r = s:table_fetch{ table="best" }
print(r)
BY-Group Processing
This section contains Python code for the analysis in the CASL version of this example, which contains details about the results.
Note: In order to run this code, the data that are described in the CASL version need to be accessible to the CAS server. One way to do this is to convert the means data to the comma-separated-value (CSV) file means.csv, convert the covdata data to the CSV file covdata.csv, and then use the following code to load the CSV files into CAS:
s.upload_file('means.csv')
s.upload_file('covdata.csv')
For more information about coding in Python, see Getting Started with SAS Viya for Python and SAS Viya: System Programming Guide.
This example illustrates how to use the runOptmodel action with BY-group processing. It uses the NLP solver to solve a MINLP portfolio optimization problem by enumerating subsets of the assets to optimize.
s.datastep.runCode(
code="""
data trials(partition=(trial) orderby=(trial));
do trial=1 to 2047;
output;
end;
run;
""", single="yes")
s.loadActionSet(actionset="optimization")
s.sessionProp.setSessOpt(messageLevel="warning")
s.runOptmodel(
code="""
/* assets and related parameters */
set ASSETS;
num return {ASSETS};
num cov {ASSETS, ASSETS} init 0;
read data means nogroupby into ASSETS=[asset] return;
read data covdata nogroupby into [asset1 asset2] cov cov[asset2,asset1]=cov;
num riskLimit init 0.00025;
num minThreshold init 0.1;
/* get the trial identifier for this BY group */
num trial;
read data trials into trial;
/* determine included assets from the binary expansion of trial */
string trial_bits = putn(trial, 'binary'||card(assets)||'.');
set ASSETS_THIS = {i in ASSETS : substr(trial_bits,i,1) ne '0'};
/* declare NLP problem for fixed set of assets */
var AssetPropVar {ASSETS} >= minThreshold <= 1;
max ExpectedReturn = sum {i in ASSETS} return[i] * AssetPropVar[i];
con RiskBound:
sum {i in ASSETS_THIS, j in ASSETS_THIS}
cov[i,j] * AssetPropVar[i] * AssetPropVar[j] <= riskLimit;
con TotalPortfolio:
sum {asset in ASSETS} AssetPropVar[asset] = 1;
put trial=;
put ASSETS_THIS=;
for {i in ASSETS diff ASSETS_THIS}
fix AssetPropVar[i] = 0;
solve with NLP / logfreq=0;
put _solution_status_=;
if _solution_status_ in {'OPTIMAL', 'BEST_FEASIBLE'} then do;
create data results from _solution_status_ ExpectedReturn;
create data solutions from [asset] AssetPropVar;
end;
""", printlevel=0, groupBy="trial")
s.sessionProp.setSessOpt(messageLevel="default")
s.loadActionSet(actionset="fedsql")
s.fedsql.execDirect(
query = """
create table best {options replace=true} as
select * from results join solutions using(trial)
where ExpectedReturn = (select max(ExpectedReturn) from results)
""")
s.table.fetch(table="best")
BY-Group Processing
This section contains R code for the analysis in the CASL version of this example, which contains details about the results.
Note: In order to run this code, the data that are described in the CASL version need to be accessible to the CAS server. One way to do this is to convert the means data to the comma-separated-value (CSV) file means.csv, convert the covdata data to the CSV file covdata.csv, and then use the following code to load the CSV files into CAS:
m <- cas.read.csv(s, "means.csv", casOut=list(name="means"))
m <- cas.read.csv(s, "covdata.csv", casOut=list(name="covdata"))
For more information about coding in R, see Getting Started with SAS Viya for R and SAS Viya: System Programming Guide.
This example illustrates how to use the runOptmodel action with BY-group processing. It uses the NLP solver to solve a MINLP portfolio optimization problem by enumerating subsets of the assets to optimize.
cas.dataStep.runCode(s,
code="
data trials(partition=(trial) orderby=(trial));
do trial=1 to 2047;
output;
end;
run;
", single="yes")
cas.builtins.loadActionSet(s,actionset="optimization")
cas.sessionProp.setSessOpt(s,messageLevel="warning")
cas.optimization.runOptmodel(s,
code="
/* assets and related parameters */
set ASSETS;
num return {ASSETS};
num cov {ASSETS, ASSETS} init 0;
read data means nogroupby into ASSETS=[asset] return;
read data covdata nogroupby into [asset1 asset2] cov cov[asset2,asset1]=cov;
num riskLimit init 0.00025;
num minThreshold init 0.1;
/* get the trial identifier for this BY group */
num trial;
read data trials into trial;
/* determine included assets from the binary expansion of trial */
string trial_bits = putn(trial, 'binary'||card(assets)||'.');
set ASSETS_THIS = {i in ASSETS : substr(trial_bits,i,1) ne '0'};
/* declare NLP problem for fixed set of assets */
var AssetPropVar {ASSETS} >= minThreshold <= 1;
max ExpectedReturn = sum {i in ASSETS} return[i] * AssetPropVar[i];
con RiskBound:
sum {i in ASSETS_THIS, j in ASSETS_THIS}
cov[i,j] * AssetPropVar[i] * AssetPropVar[j] <= riskLimit;
con TotalPortfolio:
sum {asset in ASSETS} AssetPropVar[asset] = 1;
put trial=;
put ASSETS_THIS=;
for {i in ASSETS diff ASSETS_THIS}
fix AssetPropVar[i] = 0;
solve with NLP / logfreq=0;
put _solution_status_=;
if _solution_status_ in {'OPTIMAL', 'BEST_FEASIBLE'} then do;
create data results from _solution_status_ ExpectedReturn;
create data solutions from [asset] AssetPropVar;
end;
", printlevel=0, groupBy="trial")
cas.sessionProp.setSessOpt(s, messageLevel="default")
cas.builtins.loadActionSet(s, actionset="fedSql")
cas.fedSql.execDirect(s,
query = "
create table best {options replace=true} as
select * from results join solutions using(trial)
where ExpectedReturn = (select max(ExpectedReturn) from results)
")
cas.table.fetch(s, table="best")