I am trying to solve the classic find the number problem.
I have a report which has a variance on it of some value Z. I want to be able to pinpoint what rows may be the cause of the variance Z in my data. i.e there are certain rows that may add up to the Z. In a practical sense the situation plays out as follows:
Bob: "hey Chad, your total is over by $21"
Chad: "I received a listing of the balancing entries from Bill this afternoon, I'm not too sure what is making up the $21 variance"
Bob: "Is it possible to find out what items are throwing out the variance? perhaps there are some entries that are unbalanced?"
Chad: (sigh) "I'll call my wife, I'm not going home tonight".
In most cases, Bob spends hours trying to find out which rows are causing the error, and enviably misses his dinner at home.
I have set up a sample problem with primes:
Items in "a" are primes indexed by the ID AAA,BBB,...etc.
let's say I want to find out which numbers add up to 21. (answer is AAA + DDD + FFF)
I have constructed a vector X initialized as 0's and can only be binary values. My desired result is below:
INPUT DATA = OPTMODELSETUP
:
ID Var1 AAA 1 BBB 3 CCC 5 DDD 7 EEE 11 FFF 13 GGG 17
I'm not seeming to be able to get there:
proc optmodel;
/* declare index sets */
set <str> ListSet;
set VarChoices = 1..1;
/* declare parameters */
num a {ListSet,VarChoices} init 0;
/* read the set of Variables */
read data OPTMODELSETUP into ListSet=[ID] {j in VarChoices} <a[ID,j]=col('Var'||(j))>;
print a;
/* declare the model */
var X {ListSet, VarChoices} binary init 0;
min Objective = (sum {ID in ListSet, j in VarChoices} a[ID,j]*X[ID,j]);
con sum {j in VarChoices} a[ID,j]*X[ID,j] = 31;
/* call the solver and save the results */
solve;
/*ods output PrintTable#8=Solution;*/
print X;
Is this problem expandable such that all solutions can be displayed as different columns in X? we know in real life, there may be multiple solutions in which the rows total a certain value.
Thanks in advance.
Here's one way to find a single solution (using 21 to match your example):
proc optmodel;
/* declare index sets */
set <str> ListSet;
/* declare parameters */
num a {ListSet} init 0;
/* read the set of Variables */
read data OPTMODELSETUP into ListSet=[ID] a=Var1;
print a;
/* declare the model */
var X {ListSet} binary;
con sum {ID in ListSet} a[ID]*X[ID] = 21;
/* call the solver and save the results */
solve noobj;
print X;
To get all solutions and save them to a data set, you can do this:
solve with clp / findallsolns;
create data out from [s]=(1.._NSOL_) {ID in ListSet} <col(ID)=(a[ID]*X[ID].sol[s])>;
That way outputs one observation per solution, but CREATE DATA can save the results however you want. Here's a sparse alternative:
create data out2 from [s ID]={s in 1.._NSOL_, ID in ListSet: X[ID].sol[s] > 0.5} a=a[ID];
Here's one way to find a single solution (using 21 to match your example):
proc optmodel;
/* declare index sets */
set <str> ListSet;
/* declare parameters */
num a {ListSet} init 0;
/* read the set of Variables */
read data OPTMODELSETUP into ListSet=[ID] a=Var1;
print a;
/* declare the model */
var X {ListSet} binary;
con sum {ID in ListSet} a[ID]*X[ID] = 21;
/* call the solver and save the results */
solve noobj;
print X;
To get all solutions and save them to a data set, you can do this:
solve with clp / findallsolns;
create data out from [s]=(1.._NSOL_) {ID in ListSet} <col(ID)=(a[ID]*X[ID].sol[s])>;
That way outputs one observation per solution, but CREATE DATA can save the results however you want. Here's a sparse alternative:
create data out2 from [s ID]={s in 1.._NSOL_, ID in ListSet: X[ID].sol[s] > 0.5} a=a[ID];
Yes, that is the correct interpretation.
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!
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.