topic Re: Complicated loop with PROC OPTMODEL in Mathematical Optimization, Discrete-Event Simulation, and OR

Complicated loop with PROC OPTMODEL

Alain38 — Mon, 07 Jan 2019 11:40:29 GMT

Dear all,

I would like to solve an optimization problem, every month and over many decades.

The parameters are determined beforehand with PROC IML and consist of a matrix called "MVC", which represents the covariance matrix of stock returns.

The difficulty is that each month, not only the stocks can be different, but also their number (e.g. month 1: stocks A, B, C; month 2: stocks A, C, D, E, F). Nonetheless, I could standardize the names of the stocks (let say: month1: 1,2,3; month2: 1,2,3,4,5) to append the MVC for each month, one under the other, even if they have different sizes (until this point I would manage to do it). Then, the idea would be to create a temporary dataset including solely one MVC at a time to run the optimization problem, in the spirit of rolling-window regressions:

data Work.Rwin(keep= Y X1 X2 X3 X4) / view=Work.Rwin;
do grp = 0 to nrecs-60;
     do j = 1 + grp to 60 + grp;
          set Work.Sample nobs=nrecs point=j;
          output;
          end;
     end;
stop;
run;

proc reg data=Work.Rwin outest=Work.Coeff noprint;
	by grp;
	model Y = X1 X2 X3 X4;
run;
quit;

the additional difficulty of course being that the number of observations is different for each group (grp), but hopefully could be determined thanks to the standardized name (1, 2, ..., N with N different for each grp).

The optimization code is the following:

ods select none; *Output weights without printing;
proc optmodel ;
	ods output PrintTable=Work.Weights;		/* Output results */

/* Declare the sets of parameters and data indexed by sets */
	set <string> Assets;				/* Stock names defined in the set "Assets" */
	number MVC{Assets, Assets};			/* MVC data indexed by the set "Assets" */

/* Populate the model by reading in the specific data instance */
	read data Work.MVC into Assets=[Name];		/* Read from the variable "Name" in Work.MVC */
	read data Work.MVC into [i=Name] {j in Assets} <MVC[i,j]=col(j)>;

/* Declare the variable */
	var W{Assets} >= 0;			/* Positive weights constraint */

/* Minimize the objective function (variance of the portfolio) */
	minimize Variance = sum{i in Assets, j in Assets}W[i]*MVC[i,j]*W[j];
/* Subject to the following constraints */
	con BUDGET: sum{i in Assets}W[i] = 1;

	solve with qp;
	print W;                                 /* print the optimal solution */
quit;
ods select all;

Results (vectors of weights) would be all output in the same dataset.

What do you think of this solution and if it is the best way to do, could you please help me implement it?
Otherwise, how could I proceed? I considered for example running the optimization problem directly in IML but I read on this forum that PROC OPTMODEL, being specifically designed for it, is the procedure to use for this kind of problems rather than a simple home-made IML program. There may be also much simpler solutions that I haven't thought about, so

any suggestion most welcomed, as well as assistance for writing the code,

Re: Complicated loop with PROC OPTMODEL

RobPratt — Mon, 07 Jan 2019 16:25:45 GMT

You might be interested in this Usage Note, which illustrates how to implement BY-group processing in PROC OPTMODEL:

http://support.sas.com/kb/42/332.html

Re: Complicated loop with PROC OPTMODEL

Alain38 — Tue, 08 Jan 2019 11:18:11 GMT

Thank you very much for this link, this is exactly what I need!

Could you please help me changing my PROC OPTMODEL code accordingly? My dataset MVC looks like:

data Work.MVC;
         input grp Name $ _1 _2 _3 _4 @@;
         datalines;
      1 _1 0.01  0.005 0.006 .
      1 _2 0.005 0.007 0.004 .
      1 _3 0.006 0.004 0.008 .
      2 _1 0.02  0.01  0.012 0.013
      2 _2 0.01  0.014 0.008 0.007
      2 _3 0.012 0.008 0.016 0.009
      2 _4 0.013 0.007 0.009 0.018
;

I should notably have:

%let byvar = grp;	/* BY group processing */
proc optmodel ;
/* Declare the sets of parameters and data indexed by sets */
    set <string> Assets;                /* Stock names in the set "Assets" */
    string grp {Assets};
    number MVC{Assets, Assets};

and

/* Collect values of BY variable into an index set */
	set BYSET = setof {i in Assets} &byvar.[i];
	put BYSET=;
	string by;

/* Declare the variable */
	var W{Assets_BY} >= 0;
	set Assets_BY = {i in Assets: &byvar.[i] = by};

but I am lost for the remaining of the code (read, loop...), the example being quite different from my case and I do not master the proc optmodel syntax...

Thank you so much for your help,

Re: Complicated loop with PROC OPTMODEL

RobPratt — Tue, 08 Jan 2019 16:13:55 GMT

I think the following does what you want. If you have a large number of BY groups, you might want to use PRINTLEVEL=0 in the PROC OPTMODEL statement.

%let byvar = grp;
/* BY group processing */
proc optmodel;
   /* Declare the sets of parameters and data indexed by sets */
   set OBS;
   num grp {OBS};
   set <string> Assets;				/* Stock names defined in the set "Assets" */
   set <num,str> GROUPS_ASSETS;
   number MVC{GROUPS_ASSETS, Assets};

   /* Populate the model by reading in the specific data instance */
   read data Work.MVC into Assets=[Name];		/* Read from the variable "Name" in Work.MVC */
   read data Work.MVC into OBS=[_N_] grp;
   read data Work.MVC nomiss into GROUPS_ASSETS=[k=grp i=Name] {j in Assets} <MVC[k,i,j]=col(j)>;

   /* Collect values of BY variable into an index set */
   set BYSET = setof {i in OBS} &byvar.[i];
   num by;

   /* Declare the variable */
   set OBS_BY = {i in OBS: &byvar.[i] = by};
   set Assets_BY = setof {o in OBS_BY, <(grp[o]),a> in GROUPS_ASSETS} a;
   var W{Assets_BY} >= 0;

   /* Minimize the objective function (variance of the portfolio) */
   minimize Variance = sum{i in Assets_BY, j in Assets_BY}W[i]*MVC[by,i,j]*W[j];

   /* Subject to the following constraints */
   con BUDGET: sum{i in Assets_BY}W[i] = 1;

   /* Declare parameters to store solution values */
   num W_sol {GROUPS_ASSETS};

   /* Loop over values of the BY variable, calling the QP solver once for each level.  
   Note that OBS_BY is automatically updated when the BY variable changes.  */
   do by = BYSET;
      put by=;
      solve;
      /* Store solution in numeric parameters */
      for {i in Assets_BY} W_sol[by,i] = W[i].sol;
   end;

   /* Save the solutions for all values of the BY variable simultaneously */
   create data weights from [&byvar i] W=W_sol;
quit;

Re: Complicated loop with PROC OPTMODEL

RobPratt — Tue, 08 Jan 2019 16:20:23 GMT

By the way, you can also solve these problems concurrently by replacing the DO loop with a COFOR loop:

/*   do by = BYSET;*/
   cofor {b in BYSET} do;
      by = b;

Re: Complicated loop with PROC OPTMODEL

Alain38 — Tue, 08 Jan 2019 16:26:19 GMT

I will compare and study this syntax to understand why my code, quite similar at first sight, didn't work while yours is working exactly as desired!

Thank you so much for your precious help!

Re: Complicated loop with PROC OPTMODEL

RobPratt — Thu, 10 Jan 2019 21:38:11 GMT

Here's another approach that might be simpler to digest.

%let n = 4;
data MVC_sparse(keep=grp name name2 cov);
   set MVC;
   array covarray [&n] _1-_&n;
   do j = 1 to &n;
      Name2 = '_'||put(j,1.);
      cov = covarray[j];
      if cov ne . then output;
   end;
run;

%let byvar = grp;
/* BY group processing */
proc optmodel;
   /* Declare the sets of parameters and data indexed by sets */
   set OBS;
   num grp {OBS};
   str asset_i {OBS};
   str asset_j {OBS};
   number MVC{OBS};

   /* Populate the model by reading in the specific data instance */
   read data Work.MVC_sparse into OBS=[_N_] grp asset_i=Name asset_j=Name2 MVC=cov;
   set ASSETS = setof {o in OBS} asset_i[o];

   /* Collect values of BY variable into an index set */
   set BYSET = setof {i in OBS} &byvar.[i];
   num by;

   /* Declare the variable */
   set OBS_BY = {i in OBS: &byvar.[i] = by};
   set Assets_BY = setof {o in OBS_BY} asset_i[o];
   var W{Assets_BY} >= 0;

   /* Minimize the objective function (variance of the portfolio) */
   minimize Variance = sum{o in OBS_BY}W[asset_i[o]]*MVC[o]*W[asset_j[o]];

   /* Subject to the following constraints */
   con BUDGET: sum{i in Assets_BY}W[i] = 1;

   /* Declare parameters to store solution values */
   num W_sol {BYSET, ASSETS};

   /* Loop over values of the BY variable, calling the QP solver once for each level.  
   Note that OBS_BY is automatically updated when the BY variable changes.  */
/*   do by = BYSET;*/
   cofor {b in BYSET} do;
      by = b;
      put by=;
      solve;
      /* Store solution in numeric parameters */
      for {i in Assets_BY} W_sol[by,i] = W[i].sol;
   end;

   /* Save the solutions for all values of the BY variable simultaneously */
   create data weights_sparse(where=(W ne .)) from [&byvar i] W=W_sol;
quit;