BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Alain38
Quartz | Level 8

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

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;

View solution in original post

3 REPLIES 3
RobPratt
SAS Super FREQ

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
Quartz | Level 8

@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!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Multiple Linear Regression in SAS

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.

Discussion stats
  • 3 replies
  • 704 views
  • 1 like
  • 2 in conversation