How to formulate a sumproduct difference in the objective line in Proc OptModel?

[SASAlex101]

I am setting up a model in SAS based on an excel solver problem.

in SAS I've set this up with Proc Optmodel but I can't seem to calculate the function in the objective statement that's analogous to ABS( SUMPRODUCT(var1..10,var11..20) - szone) to work... The goal is for each row, the difference between each szone and sumproduct should be minimal.

data WORK.sMatrix;
Infile datalines delimiter='#';
input s szone var7 var8 var9 var10 var11 var12;
datalines;
824801#3#9#4#6#6#6#6
696455#3#6#6#6#6#6#3
920946#1#6#1#6#6#6#6
;
Run;

proc optmodel;
set SSET;
set JSET = 1..6;
num a {SSET, JSET};
num szone {SSET};

read data sMatrix into SSET=[s] {j in JSET} <a[s,j] = col('Var'||(j+6))> szone;

print a szone;

var X {SSET, JSET} binary;

min Objective = (sum {s in SSET, j in JSET} a[s,j]*X[s,j] - szone) / (sum {s in SSET} szone[s]);

con OnceChoice {s in SSET}:
sum {j in JSET} X[s,j] = 1;

solve;
print X;
quit;

[RobPratt]

I think you want this instead:

min Objective = (sum {s in SSET} abs(sum {j in JSET} a[s,j]*X[s,j] - szone[s])) / (sum {s in SSET} szone[s]);

The ABS introduces nonlinearity, but in this case you can use the LINEARIZE option in SAS Viya to automatically linearize the problem:

solve linearize;

After the solve, you can then print the two parts of the numerator:

print {s in SSET} (sum {j in JSET} a[s,j]*X[s,j]) szone;

The resulting optimal solution has objective value 1/7, and the two PRINT statements yield:

X
	1	2	3	4	5	6
696455	0	0	0	0	0	1
824801	0	1	0	0	0	0
920946	0	1	0	0	0	0

[1]		szone
696455	3	3
824801	4	3
920946	1	1

[SASAlex101]

Hi Rob. thanks! the suggestion for szone[s] errored. but I think I got this to work: 

min Objective = (sum {s in SSET, j in JSET} a[s,j]*X[s,j]) - (sum{s in SSET} szone[s]) / (sum {s in SSET} szone[s]);

You're right the problem is non-linear with ABS, so I also tried with 

min Objective = ((sum {s in SSET, j in JSET} a[s,j]*X[s,j]) - (sum{s in SSET} szone[s]))^2 / (sum {s in SSET} szone[s]);

and SAS didn't like it. 
I then tried the linearize option but it errored. the following code throws an error: 

data WORK.sMatrix;
Infile datalines delimiter='#';
input s szone var7 var8 var9 var10 var11 var12;
datalines;
824801#3#9#4#6#6#6#6
696455#3#6#6#6#6#6#3
920946#1#6#1#6#6#6#6
;
Run;

proc optmodel;
ods output PrintTable#3=expt3;

set SSET;
set JSET = 1..6;
num a {SSET, JSET};
num szone {SSET};

read data  sMatrix into SSET=[s] {j in JSET} <a[s,j]=col('Var'||(j+6))> szone;

print a szone;

var X {SSET, JSET} binary;

constraint OnceChoice {s in SSET}: sum {j in JSET} X[s,j] = 1;

min Objective = abs((sum {s in SSET, j in JSET} a[s,j]*X[s,j]) - (sum{s in SSET} szone[s])) / (sum {s in SSET} szone[s]);

solve linearize; 
print X;

quit;

ERROR 22-322: Syntax error, expecting one of the following: /, OBJECTIVE, WITH.

ERROR 76-322: Syntax error, statement will be ignored.
I'm using SAS EG 8.2 (8.2.0.1201) (64 bit)

Thanks Rob!

[RobPratt · Accepted solution]

The LINEARIZE option to automatically linearize is available only in SAS Optimization (SAS Viya) and not SAS/OR (SAS 9), but you can manually linearize as follows:

var Abs {SSET};
con AbsCon1 {s in SSET}:
   Abs[s] >= sum {j in JSET} a[s,j]*X[s,j] - szone[s];
con AbsCon2 {s in SSET}:
   Abs[s] >= -sum {j in JSET} a[s,j]*X[s,j] + szone[s];
min Objective = (sum {s in SSET} Abs[s]) / (sum {s in SSET} szone[s]);

[SASAlex101]

thanks Rob. this seemed to work. Can you provide any resources that you learned from on how to linearize non linear problems? Your help is much appreciated.

I'd also like your take on this approach that I tried... but it took a while to calculate. If you remove the binary condition on the X variable and put two constraints
constraint XlowerBound {s in SSET}: max {j in JSET} X[s,j] = 1;
constraint XupperBound {s in SSET}: Min {j in JSET} X[s,j] = 0;
this allows the variables to 'buldge' a bit and then one would just take the value in the X matrix closest to 1 and consider any small numbers as 0?
696455 (0.0000448520) (0.0000855790) 0.0003038620 (0.0000649350) (0.0001077940) 1.0000031530
824801 0.0000962030 0.9997714850 (0.0001034780) 0.0001833090 0.0003918160 (0.0001043860)
920946 0.0002939980 1.0000106140 0.0000202700 (0.0001121620) (0.0001144810) (0.0001099790)

seems to be the same solution?

thanks Rob;

[SASAlex101]

Thanks Rob. I'd love your take on an alternate solution that seemed to go quite a bit slower but yeilded the same result (kinda)
remove the binary type on the X variable
constraints:
constraint OnceChoice {s in SSET}: sum {j in JSET} X[s,j] = 1;
constraint XlowerBound {s in SSET}: max {j in JSET} X[s,j] = 1;
constraint XupperBound {s in SSET}: Min {j in JSET} X[s,j] = 0;

then the result is real numbers that seem are almost exact and one can just pick the one closest to 1 and call small numbers 0. What do you think of this approach?

Also I'd love to know any resources on linearization that you may have used to gain your knowledge.

Thanks Rob;

[RobPratt]

Relaxing integrality on X is a reasonable approach to try for cases when you cannot linearize, but it is not guaranteed to yield anything close to an integer solution, so you were a bit lucky here.  Also, the MIN and MAX operators introduce additional nonlinearity that you should avoid.  To relax integrality, the simplest change is instead to use the RELAXINT option:

solve relaxint;

Alternatively, you can change the declaration of X:

var X {SSET, JSET} >= 0 <= 1;

Another way is to set the .LB and .UB suffixes:

var {SSET, JSET};
for {s in SSET, j in JSET} do;
   X[s,j].lb = 0;
   X[s,j].ub = 1;
end;

Yet another way is to impose explicit constraints:

/* two constraints */
con XlowerBound {s in SSET, j in JSET}:
   X[s,j] >= 0;
con XupperBound {s in SSET, j in JSET}:
   X[s,j] <= 1;

/* one range constraint */
con XBound {s in SSET, j in JSET}:
   0 <= X[s,j] <= 1;

The presolver will replace such constraints with bounds, anyway, so I recommend just using RELAXINT here.

Regarding manual linearization, you can find numerous examples in this book.   Look for "linearization" in the Subject Index in the pdf.

The new automatic linearization techniques are discussed here.

[SASAlex101]

Thanks Rob. This is very helpful. Last question if you had the time, I mapped out the constraints and I'm not fully understanding how it represents an abs() function...


con AbsCon1 {s in SSET}:
Abs[s] >= sum {j in JSET} a[s,j]*X[s,j] - szone[s];

con AbsCon2 {s in SSET}:
Abs[s] >= -sum {j in JSET} a[s,j]*X[s,j] + szone[s];

min Objective = (sum {s in SSET} Abs[s]) / (sum {s in SSET} szone[s]);

for one particular row we would have szone =4 and some choices 1 2 3 4 and 5.

szone  choice    AbsCon1   AbsCon2   met
4               3           -1                -1                yes
4               2            2                -2                 no
4               1            3                -3                 no
4               5           -1                -1                yes
4               4            0                 0                 yes

min (-1, -1, 0) = -1  either -1 or -1 corresponding to choice 3 or 5. Shouldnt the correct answer be 0? corresponding to a choice of 4?

[RobPratt]

The two columns labeled AbsCon1 and AbsCon2 in your table should always have opposite signs.  When you correct the table, I think you will see that Abs[s] >= 0.  You can also deduce that by adding the two constraints: everything on the right-hand side cancels in pairs, yielding 2*Abs[s] >= 0, equivalently, Abs[s] >= 0.

[SASAlex101]

You're right. My mistake. Thanks so much Rob.