Hi.
It looks like the subset sum variant of the knapsack problem, which is a combinatorial optimization problem.
https://en.wikipedia.org/wiki/Knapsack_problem
If you have a SAS/IML licensed on your site you'll have at your disposal procedures who can handle this, see the following example:
http://support.sas.com/documentation/cdl/en/imlug/66112/HTML/default/imlug_geneticalgs_sect013.htm
R will handle that also (with the appropriate package).
That said, it can be done programmatically, but it's not the best thing to do in SAS/Base
One approach (listed above) would be to generate all possible combinations under the target value and then pick the best.
If the variables of the problem can change, than it might be not very pratical to code like that.
So it can be done recursively at a performance cost, but then you only have to run it once and keep the solution for everytime you need.
Here's my solution, which will produce every possible combination thus giving you always the best match, which is = target or best with lesser bottles. Calculations are done in macro language, which might be not easy to understand, but solving the problem is not an easy task 🙂
* recursively search best combination;
%macro recursive_subset_sum(VALUES,SUM,TARGET,SUBSET,INDEX,INDEXN,SIZE,_SUBSET);
%local VALUES SUM TARGET SUBSET INDEX INDEXN SIZE _SUM _VALUE _INDEX;
%let _SUM=%eval(&SUM+%scan(&VALUES,&INDEX));
%if &INDEX eq &INDEXN or
%sysfunc(countw(%str(&SUBSET))) eq &SIZE or
&SUM eq &TARGET or &_SUM gt &TARGET %then %do;
%if &_SUM gt &TARGET and %scan(&&&_SUBSET,-1) lt &SUM %then
%let &_SUBSET=&SUBSET ∑
%end;
%else %do;
%let _INDEX=%eval(&INDEX+1);
%let _VALUE=%scan(&VALUES,&_INDEX);
%recursive_subset_sum(&VALUES,&SUM,&TARGET,&SUBSET,&_INDEX,&INDEXN,&SIZE,&_SUBSET)
%recursive_subset_sum(&VALUES,&_SUM,&TARGET,&SUBSET &INDEX,&_INDEX,&INDEXN,&SIZE,&_SUBSET)
%end;
%mend recursive_subset_sum;
* solve given problem;
%macro solve(VALUES,TARGET,SIZE);
%let VALUESN=%sysfunc(countw(%str(&VALUES)));
%let FIXVALUES=;
%do _I=1 %to &SIZE;
%let FIXVALUES=&FIXVALUES &VALUES;
%end;
%let _SOLUTION=0;
%recursive_subset_sum(&FIXVALUES,0,&TARGET,,1,%eval(&VALUESN*&SIZE),&SIZE,_SOLUTION)
%let SOLUTION=;
%do _I=1 %to %sysfunc(countw(%str(&_SOLUTION)))-1;
%let SOLUTION=&SOLUTION %eval(%sysfunc(mod(%scan(&_SOLUTION,&_I)-1,&VALUESN))+1;
%end;
&SOLUTION
%mend solve;
%let TARGET=105;
%let VALUES=30 50 80 50 20 50;
%let SIZE=6;
%put Best Solution = %left%trim(%solve(&VALUES,&TARGET,&SIZE));
And to keep it in a dataset
data test;
length S $50;
do i = 10 to 290 by 5;
S=resolve(cats('%solve(',"&VALUES,",i,",&SIZE)"));
put _all_; * show me;
output;
end;
run;
Hope it helps.
Daniel Santos @ www.cgd.pt
... View more