BookmarkSubscribeRSS Feed
ismahero2
Obsidian | Level 7

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.

Capture.JPG

9 REPLIES 9
RobPratt
SAS Super FREQ

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 = ⌖
   solve;
   print wages UseRecord;
quit;

ismahero2
Obsidian | Level 7

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

ERROR: Procedure OPTOMODEL not found.

Reeza
Super User

Do you have SAS/OR licensed?

ismahero2
Obsidian | Level 7

Yes I think i have a license.

any way I can do it without optomodel?

RobPratt
SAS Super FREQ

You have an extra O in the procedure name.

ismahero2
Obsidian | Level 7

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

Any other options that you can think of?

PGStats
Opal | Level 21

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

PG
Ksharp
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 9 replies
  • 6805 views
  • 0 likes
  • 6 in conversation