proc optmodel / by-group and multiple data sets

[Alain38]

Hello,

I am having a hard time processing an optmodel by group when there are multiple data sets.

The following code works perfectly for a single group:

proc optmodel;
	var W{1..6};

	num MVC{1..6, 1..6} = [0.00111	0.00069	0.00073	0.00053	0.00070	0.00123
		  	       0.00069	0.00081	0.00070	0.00043	0.00064	0.00117
		   	       0.00073	0.00070	0.00091	0.00050	0.00075	0.00126
		   	       0.00053	0.00043	0.00050	0.00133	0.00120	0.00185
		   	       0.00070	0.00064	0.00075	0.00120	0.00164	0.00238
		   	       0.00123	0.00117	0.00126	0.00185	0.00238	0.00430];

	num E{1..6} = [0.00690	0.00360	0.00138	-0.00169	0.00436	-0.01439];
	num Var_Rm = 0.001483;

	maximize Expected = sum{i in 1..6}W[i]*E[i];
	con sum{i in 1..6, j in 1..6}W[i]*MVC[i,j]*W[j] = Var_Rm;
	con BUDGET: sum{i in 1..6}W[i] = 1;

	solve; print W;
quit;

but when I add other groups, I obtain error with the READ DATA statements of parameters "E" and "Var_Rm":

data Cov_matrix; 
	input grp _1 _2 _3 _4 _5 _6 Name $5.; 
    datalines; 
1	0.00111	0.00069	0.00073	0.00053	0.00070	0.00123	_1
1	0.00069	0.00081	0.00070	0.00043	0.00064	0.00117	_2
1	0.00073	0.00070	0.00091	0.00050	0.00075	0.00126	_3
1	0.00053	0.00043	0.00050	0.00133	0.00120	0.00185	_4
1	0.00070	0.00064	0.00075	0.00120	0.00164	0.00238	_5
1	0.00123	0.00117	0.00126	0.00185	0.00238	0.00430	_6
2	0.00112	0.00070	0.00073	0.00053	0.00070	0.00122	_1
2	0.00070	0.00080	0.00070	0.00043	0.00064	0.00117	_2
2	0.00073	0.00070	0.00090	0.00050	0.00075	0.00126	_3
2	0.00053	0.00043	0.00050	0.00132	0.00120	0.00185	_4
2	0.00070	0.00064	0.00075	0.00120	0.00165	0.00238	_5
2	0.00122	0.00117	0.00126	0.00185	0.00238	0.00429	_6
;

data Expected; 
	input grp _1 _2 _3 _4 _5 _6; 
    datalines; 
1	0.00690	0.00360	0.00138	-0.00169	0.00436	-0.01439
2	0.00890	0.00320	0.00118	-0.00165	0.00426	-0.01449
;

data Target_variance; 
	input grp Var_Rm; 
    datalines; 
1	0.001483
2	0.000784
;

%let byvar = grp;
proc optmodel printlevel=0;
 	/* Declare index sets */
	set OBS;			/* Define the index set "OBS" */	
	set <str> ASSETS;		/* Define the index set "Assets" */
	set <num,str> GROUPS_ASSETS;	/* Define the index set "GROUPS_ASSETS" */

	/* Declare  parameters */
	num grp{OBS};			/* "grp" data is indexed by the index set "OBS" */
	num MVC{GROUPS_ASSETS, ASSETS};	/* "MVC" data is indexed by the index set "GROUPS_ASSETS" for rows and "Assets" for columns */
	num E{GROUPS_ASSETS, ASSETS};	/* "E" data is indexed by the index set "GROUPS_ASSETS" for rows and "Assets" for columns */
	num Var_Rm{GROUPS_ASSETS};	/* "Var_Rm" data indexed by the index set "GROUPS_ASSETS" for rows */

	/* Read the index sets and parameters from the input data sets */
	read data Cov_matrix into ASSETS=[Name];		/* Read "Assets" from the variable "Name" in Work.MVC */
	read data Cov_matrix into OBS=[_N_] grp;		/* Read ?? Distinction between key-column and column? */
	read data Cov_matrix nomiss into GROUPS_ASSETS=[k=grp i=Name] {j in ASSETS} <MVC[k,i,j]=col(j)>;

	read data Expected nomiss into [k=grp] {j in ASSETS} <E[k,j]=col(j)>;

	read data Target_variance into [k=grp] {Var_Rm} <Var_Rm[k]>;

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

	/* Declare variables and objective */
	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};

	maximize Expected = sum{i in Assets_BY}W[i]*E[by,i];

	con sum{i in Assets_BY, j in Assets_BY}W[i]*MVC[by,i,j]*W[j] = Var_Rm[by];
	con BUDGET: sum{i in Assets_BY}W[i] = 1;

	num W_sol {GROUPS_ASSETS};
	do by = BYSET;
		put by=;
		solve with NLP / feastol = 1E-7;
		for {i in Assets_BY} W_sol[by,i] = W[i].sol;
	end;

	create data Weights from [&byvar i] W=W_sol;
quit;

So (once again, I'm sorry) I would appreciate some help,

[RobPratt · Accepted solution]

Please change your declaration of E as follows:

num E{GROUPS_ASSETS};

Also please change your declaration and read of Var_Rm to:

set GROUPS; 
num Var_Rm{GROUPS};	/* "Var_Rm" data indexed by the index set "GROUPS" for rows */
read data Target_variance into GROUPS=[grp] Var_Rm;

[Alain38]

@RobPrattI just can't thank you enough for your huge help and don't know what I would do without. This proc optmodel is the hardest procedure in SAS in my opinion but it is so useful, and I hope to be finally self-reliant with it now. Thank you!

[RobPratt]

Glad to help!