Here is a solution that may work in real life, even with 70 observations:
%let sum_wanted=15;
data have; /* just som test data */
do i=1 to 20;
output;
end;
run;
data _null_; /* get number of observations */
call symputx('nobs',nobs);
stop;
set have nobs=nobs;
run;
proc sort data=have;
by i;
run;
/* a recursive macro to search combinations of length up to &depth */
%macro search(iter,depth);
%local i prev_i prev_sum;
%if &iter=1 %then %do;
%let prev_i=0;
%let prev_sum=0;
%end;
%else %do;
%let prev_i=_I%eval(&iter-1);
%let prev_sum=_sum%eval(&iter-1);
%end;
do _I&iter=&prev_i+1 to &nobs;
_sum&iter=&prev_sum+temp(_I&iter);
if _sum&iter>&sum_wanted then
leave;
if _sum&iter=&sum_wanted then do;
comb_no+1;
%do i=1 %to &iter;
set have point=_i&i;
output;
%end;
end;
%if &iter<&depth %then %do;
else do;
%search(%eval(&iter+1),&depth);
end;
%end;
_sum&iter=&prev_sum-temp(_I&iter);
end;
%mend;
options mprint;
data want;
array temp(&nobs) 8 _temporary_;
do _N_=1 to &nobs;
set have;
temp(_N_)=i;
end;
%search(1,8);
keep i comb_no; /* you may want to add some key variables here */
run;
The first idea is to sort the data in ascending order, so that you can leave the loop as soon as the values get to large for a specific combination.
The second idea is to place the values in a temporary array, so that you can go through them fast.
The third is to write a recursive macro, which starts out by looking at "combinations" of length 1, then, for each of those, looking at combinations of length 2, etc. - up to the value &depth.
Output is "long", you get a row for each combination and value, you may have to use sort and transpose to get the output you want.
... View more
