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 02-20-2021 10:53 PM
(991 views)

Hello, I'm a very new SAS user and would like to know if there is any solution to the problem I am facing. I have read through many questions regarding replicating Solver in excel and most of them require SAS/OR, which I unfortunately don't have access to. Is there any way to achieve the same result without PROC OPTMODEL?

Please see the attached file for details of the problem.

1 ACCEPTED SOLUTION

Accepted Solutions

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

With OPTMODEL:

```
data indata;
input Weight Probability;
Probability = Probability / 100;
datalines;
2 0.001
34 0.010
56 0.030
90 0.070
27 0.120
10 1.100
;
proc optmodel;
set OBS;
num weight {OBS};
num probability {OBS};
read data indata into OBS=[_N_] weight probability;
num logit {i in OBS} = log(probability[i]/(1-probability[i]));
print weight probability logit;
var X;
impvar OptimizedLogit {i in OBS} = logit[i] + X;
impvar OptimizedProbability {i in OBS} = 1/(1+exp(-OptimizedLogit[i]));
impvar WeightedAverage = (sum {i in OBS} weight[i] * OptimizedProbability[i]) / (sum {i in OBS} weight[i]);
num target = 0.003;
min Error = abs(WeightedAverage - target);
solve with nlp / ms;
print X WeightedAverage OptimizedLogit OptimizedProbability;
quit;
```

Solution Summary | |
---|---|

Solver | Multistart NLP |

Algorithm | Interior Point Direct |

Objective Function | Error |

Solution Status | Best Feasible |

Objective Value | 6.505213E-17 |

Number of Starts | 100 |

Number of Sample Points | 320 |

Number of Distinct Optima | 73 |

Random Seed Used | 7412291 |

Optimality Error | 0.0029506036 |

Infeasibility | 0 |

Presolve Time | 0.00 |

Solution Time | 0.05 |

X | WeightedAverage |
---|---|

1.0799 | 0.003 |

[1] | OptimizedLogit | OptimizedProbability |
---|---|---|

1 | -10.4331 | 0.000029442 |

2 | -8.1304 | 0.000294370 |

3 | -7.0316 | 0.000882766 |

4 | -6.1839 | 0.002058189 |

5 | -5.6444 | 0.003524902 |

6 | -3.4189 | 0.031708828 |

With DATA step, using discrete steps on interval [0,2]:

```
%let numObs = 6;
data sample;
array Weight[&numObs] (2 34 56 90 27 10);
array Probability[&numObs] (0.00001 0.0001 0.0003 0.0007 0.0012 0.011);
array Logit[&numObs];
SumOfWeights = sum(of Weight[*]);
do i = 1 to &numObs;
Logit[i] = log(Probability[i]/(1-Probability[i]));
end;
array OptimizedLogit[&numObs];
array OptimizedProbability[&numObs];
do X = 0 to 2 by 0.01;
WeightedAverage = 0;
do i = 1 to &numObs;
OptimizedLogit[i] = Logit[i] + X;
OptimizedProbability[i] = 1/(1+exp(-OptimizedLogit[i]));
WeightedAverage + Weight[i] * OptimizedProbability[i];
end;
WeightedAverage = WeightedAverage / SumOfWeights;
output;
end;
run;
proc sgplot data=sample;
scatter x=X y=WeightedAverage;
refline 0.003;
run;
```

3 REPLIES 3

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

With OPTMODEL:

```
data indata;
input Weight Probability;
Probability = Probability / 100;
datalines;
2 0.001
34 0.010
56 0.030
90 0.070
27 0.120
10 1.100
;
proc optmodel;
set OBS;
num weight {OBS};
num probability {OBS};
read data indata into OBS=[_N_] weight probability;
num logit {i in OBS} = log(probability[i]/(1-probability[i]));
print weight probability logit;
var X;
impvar OptimizedLogit {i in OBS} = logit[i] + X;
impvar OptimizedProbability {i in OBS} = 1/(1+exp(-OptimizedLogit[i]));
impvar WeightedAverage = (sum {i in OBS} weight[i] * OptimizedProbability[i]) / (sum {i in OBS} weight[i]);
num target = 0.003;
min Error = abs(WeightedAverage - target);
solve with nlp / ms;
print X WeightedAverage OptimizedLogit OptimizedProbability;
quit;
```

Solution Summary | |
---|---|

Solver | Multistart NLP |

Algorithm | Interior Point Direct |

Objective Function | Error |

Solution Status | Best Feasible |

Objective Value | 6.505213E-17 |

Number of Starts | 100 |

Number of Sample Points | 320 |

Number of Distinct Optima | 73 |

Random Seed Used | 7412291 |

Optimality Error | 0.0029506036 |

Infeasibility | 0 |

Presolve Time | 0.00 |

Solution Time | 0.05 |

X | WeightedAverage |
---|---|

1.0799 | 0.003 |

[1] | OptimizedLogit | OptimizedProbability |
---|---|---|

1 | -10.4331 | 0.000029442 |

2 | -8.1304 | 0.000294370 |

3 | -7.0316 | 0.000882766 |

4 | -6.1839 | 0.002058189 |

5 | -5.6444 | 0.003524902 |

6 | -3.4189 | 0.031708828 |

With DATA step, using discrete steps on interval [0,2]:

```
%let numObs = 6;
data sample;
array Weight[&numObs] (2 34 56 90 27 10);
array Probability[&numObs] (0.00001 0.0001 0.0003 0.0007 0.0012 0.011);
array Logit[&numObs];
SumOfWeights = sum(of Weight[*]);
do i = 1 to &numObs;
Logit[i] = log(Probability[i]/(1-Probability[i]));
end;
array OptimizedLogit[&numObs];
array OptimizedProbability[&numObs];
do X = 0 to 2 by 0.01;
WeightedAverage = 0;
do i = 1 to &numObs;
OptimizedLogit[i] = Logit[i] + X;
OptimizedProbability[i] = 1/(1+exp(-OptimizedLogit[i]));
WeightedAverage + Weight[i] * OptimizedProbability[i];
end;
WeightedAverage = WeightedAverage / SumOfWeights;
output;
end;
run;
proc sgplot data=sample;
scatter x=X y=WeightedAverage;
refline 0.003;
run;
```

- 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 can examine the resulting data set to find that X = 1.08 yields WeightedAverage = 0.0030004091.

Various other ways to solve nonlinear equations in SAS are described in the blog post https://blogs.sas.com/content/iml/2018/02/28/solve-system-nonlinear-equations-sas.html

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.