Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-21-2021 02:57 PM
(483 views)

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;

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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]);
```

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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]);
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.