Fluorite | Level 6

## Excel Solver code for SAS

Hi, I am trying to find a way to create a code in SAS that would imitate the Excel Solver Add in. I am trying to obtain a list of records that when you sum some of them together will give you the target you are looking for. For example the data below was pasted from excel spreadsheet where I use Solver, You will see 10 records, but only the sum of 5 of them will give you the target figure. My ideal solution will be an exact match, but I can have a tolerance included in the match of 1% difference between the sum of records and the target figure. Can anyone help me with this. I am kind of new in programming and I am having a difficult time creating this.

9 REPLIES 9
SAS Super FREQ

## Re: Excel Solver code for SAS

Here's one way to do it with the MILP solver in PROC OPTMODEL, by minimizing the absolute error between the sum and the target:

data indata;
input wages;
datalines;
14611
250125
2000135
850
502525
55458
248788
2211
11252458
2251215
;

%let target = 11520255;

proc optmodel;
set RECORDS;
num wages {RECORDS};
read data indata into RECORDS=[_N_] wages;
var UseRecord {RECORDS} binary;
var Surplus >= 0;
var Slack >= 0;
min Error = Surplus + Slack;
con Error_con:
sum {i in RECORDS} wages * UseRecord - Surplus + Slack = &target;
solve;
print wages UseRecord;
quit;

Fluorite | Level 6

## Re: Excel Solver code for SAS

I am having this error...  Does this means I can do it?

Super User

## Re: Excel Solver code for SAS

Fluorite | Level 6

## Re: Excel Solver code for SAS

Yes I think i have a license.

any way I can do it without optomodel?

SAS Super FREQ

## Re: Excel Solver code for SAS

You have an extra O in the procedure name.

Fluorite | Level 6

## Re: Excel Solver code for SAS

Thats ok, I just found out we dont have that OPTMODEL available to use in out license.

Any other options that you can think of?

Opal | Level 21

## Re: Excel Solver code for SAS

For n<= 20, you could do an exhaustive search in a datastep with function GRAYCODE. - PG

PG
SAS Employee

Super User

## Re: Excel Solver code for SAS

Check this session. HaiKuo has already give you answer by using graycode() . But first of all ,we should thank PG who bring graycode() in front of us.

https://communities.sas.com/message/139013#139013

Good Luck.

Xia Keshan

Discussion stats
• 9 replies
• 5733 views
• 0 likes
• 6 in conversation