## Cycling through combinations of offices

Solved
Occasional Contributor
Posts: 5

# Cycling through combinations of offices

In a given territory, I have entity, offices that roll up to the entity, and customer IDs for each office (as in table below). Offices in a given entity can share one or more customers, and I want to be able to cycle through different combinations of all offices in a given territory (whether they belong to the same entity or not) and determine a unique count of customers. For example, if I wanted to get the optimal combination of 60% of the offices listed below (3 offices), where optimal is defined as maximizing the number of unique customers, I would have the counts for the following combinations:

A, B, C = 6 unique IDs

A, B, D = 5 unique IDs

A, D, E = 5 unique IDs

...

I need to be able to select a percentage or counts of offices as the territory level and cycle through. Any help is greatly appreciated!!

Territory     Entity       Office     Customer

Atlanta        J Inc               A                 1

Atlanta        J Inc               A                 2

Atlanta        J Inc               A                 3

Atlanta        J Inc               B                 2

Atlanta        J Inc               B                 4

Atlanta        J Inc               C                 2

Atlanta        J Inc               C                 3

Atlanta        J Inc               C                 5

Atlanta        J Inc               C                 8

Atlanta        H Inc               D                 9

Atlanta        H Inc               E                 10

Accepted Solutions
Solution
‎08-05-2015 10:43 AM
Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

You just need the combination which has three members, right ?

### Code: Program5.sas

`data have;input Territory \$   Entity  & \$20.   Office  \$   Customer;cards;Atlanta J Inc A 1Atlanta J Inc A 2Atlanta J Inc A 3Atlanta J Inc B 2Atlanta J Inc B 4Atlanta J Inc C 2Atlanta J Inc C 3Atlanta J Inc C 5Atlanta J Inc C 8Atlanta H Inc D 9Atlanta H Inc E 10;run;data want;if _n_ eq 1 then do; if 0 then set have; declare hash ha(dataset:'have',multidata:'y'); ha.definekey('Territory','Office'); ha.definedata('Customer'); ha.definedone();length list \$ 200 key 8;  declare hash c(); c.definekey('key'); c.definedone();end;set have;by Territory;length list \$ 200;array x{9999} \$ 40 _temporary_;if first.Territory then do; n=0;call missing(of x{*});c.clear();end;if Office not in x then do;n+1;x{n}=Office;end;if last.Territory then do; do i=1 to n;  do j=i+1 to n;   do k=j+1 to n;   c.clear();   Office=x{i};   rc=ha.find();   do while(rc=0);   key=Customer;   rx=c.add();   rc=ha.find_next();   end;      Office=x{j};   rc=ha.find();   do while(rc=0);   key=Customer;   rx=c.add();   rc=ha.find_next();   end;      Office=x{k};   rc=ha.find();   do while(rc=0);   key=Customer;   rx=c.add();   rc=ha.find_next();   end;      list=catx(',',x{i},x{j},x{k});count=c.num_items; output;   end;  end; end; end;keep list count;run;`

All Replies
Solution
‎08-05-2015 10:43 AM
Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

You just need the combination which has three members, right ?

### Code: Program5.sas

`data have;input Territory \$   Entity  & \$20.   Office  \$   Customer;cards;Atlanta J Inc A 1Atlanta J Inc A 2Atlanta J Inc A 3Atlanta J Inc B 2Atlanta J Inc B 4Atlanta J Inc C 2Atlanta J Inc C 3Atlanta J Inc C 5Atlanta J Inc C 8Atlanta H Inc D 9Atlanta H Inc E 10;run;data want;if _n_ eq 1 then do; if 0 then set have; declare hash ha(dataset:'have',multidata:'y'); ha.definekey('Territory','Office'); ha.definedata('Customer'); ha.definedone();length list \$ 200 key 8;  declare hash c(); c.definekey('key'); c.definedone();end;set have;by Territory;length list \$ 200;array x{9999} \$ 40 _temporary_;if first.Territory then do; n=0;call missing(of x{*});c.clear();end;if Office not in x then do;n+1;x{n}=Office;end;if last.Territory then do; do i=1 to n;  do j=i+1 to n;   do k=j+1 to n;   c.clear();   Office=x{i};   rc=ha.find();   do while(rc=0);   key=Customer;   rx=c.add();   rc=ha.find_next();   end;      Office=x{j};   rc=ha.find();   do while(rc=0);   key=Customer;   rx=c.add();   rc=ha.find_next();   end;      Office=x{k};   rc=ha.find();   do while(rc=0);   key=Customer;   rx=c.add();   rc=ha.find_next();   end;      list=catx(',',x{i},x{j},x{k});count=c.num_items; output;   end;  end; end; end;keep list count;run;`
Occasional Contributor
Posts: 5

## Re: Cycling through combinations of offices

This is awesome! Thanks!! Quick question. Is there an easy way to make the number of combinations a variable? Right now, to go from 3 to 4 combinations I have to modify a few parts in the code. Is there a way code it such that I only have to put in the number 3 or 4?

Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

" Is there an easy way to make the number of combinations a variable? "

No, there is not a easy way to go .If you want get the combination of 4 , that is easy to change .

But If you want define a 3 or 4 like a macro variable, I am afraid that I have make a macro .

And If you want all the combination 1 ,2,3,4,........ , I can also make it happen via function GRAYCODE() .

Xia Keshan

Occasional Contributor
Posts: 5

## Re: Cycling through combinations of offices

I am able to change the combination to 4, 5, ... etc. The problem is I may need to run combinations up to 50 or 200 and it takes a while for me to change the code to do that. That is why I was hoping to get a macro to do it. Any help is greatly appreciated.

Abdou

Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

OK. Let me think about it . Give me some time .

Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

OK. I think I get it all figure out.

### Code: Program1.sas

`data have;input Territory \$   Entity   \$  Office  \$   Customer;cards;Atlanta JInc A 1Atlanta JInc A 2Atlanta JInc A 3Atlanta JInc B 2Atlanta JInc B 4Atlanta JInc C 2Atlanta JInc C 3Atlanta JInc C 5Atlanta JInc C 8Atlanta HInc D 9Atlanta HInc E 10BAtlant JInc A 1BAtlant JInc A 2BAtlant JInc A 3BAtlant JInc B 2BAtlant JInc B 4BAtlant JInc C 2BAtlant JInc C 3BAtlant JInc C 5BAtlant JInc C 8BAtlant HInc D 9;run;proc sql;select max(n) into : n  from (select count(distinct Office ) as n from have group by Territory );quit;%let k=3;data want;if _n_ eq 1 then do; if 0 then set have; declare hash ha(dataset:'have',multidata:'y'); ha.definekey('Territory','Office'); ha.definedata('Customer'); ha.definedone();length list \$ 2000 key 8;  declare hash c(); c.definekey('key'); c.definedone();end;set have;by Territory;array x{&n} \$ 40 _temporary_;array y{&n};if first.Territory then do; n=0;call missing(of x{*});end;if Office not in x then do;n+1;x{n}=Office;end;if last.Territory then do; k=-1; do ii=1 to 2**dim(y);  c.clear();call missing(list);count=0;  rx=graycode(k, of y{*});  if k=&k then do;   do i=1 to n;   if y{i}=1 then count+1;   end;   if count=&k then do;   do i=1 to n;   if y{i}=1 then do;   list=catx(',',list,x{i});   Office=x{i};   rc=ha.find();   do while(rc=0);   key=Customer;   c.replace();   rc=ha.find_next();   end;   end;    end;   unique_IDs=c.num_items; output;   end;   end; end;end;keep Territory list unique_IDs;run;`

Xia Keshan

Occasional Contributor
Posts: 5

## Re: Cycling through combinations of offices

This appears to work!!! Thanks!!

Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

It is not suitable for the combination which have many members.

If n=10  , You will get 2**10=1024 combination , that is really horrible .

I suggest to make a macro to wrap every kind of combination Like :

%if &k=2 %then %do;

........

%end;

%else %if &k=3 %then %do;

..................

etc .

Xia Keshan

Occasional Contributor
Posts: 5

## Re: Cycling through combinations of offices

How do you suggest I do it? I have 1,600 offices and want to be able to pick the most optimal 750, or 800, or 850.

When I use the new code you sent me, I get an error saying too many arguments for graycode.

Thanks.

Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

That couldn't be. You gotta know How big number of combination it would generate . You can

` NOT `
use such algorithm( enumerate all the combinations).

You need some sophisticated algorithm like SIMPLEX(or cutting branch) method .That is beyond my knowledge . and you need SAS/OR for such large scale problem .

Suggest you post it at  Mathematical Optimization and Operations Research , maybe some SAS/OR expert could help you.

Xia Keshan

Message was edited by: xia keshan

Super User
Posts: 10,787

## Re: Cycling through combinations of offices

Posted in reply to AbdouTazi

HaHa, I think I can solve it by Genetic Algorithm. I love GA a hell of a lot.

Change n=10, if you want get the combination which have 10 members, and I pick up the highest count of unique ids for the combination.

NOTE: This code can only process one Territory one time .

```data have;
input Territory \$   Entity   \$  Office  \$   Customer;
cards;
Atlanta        JInc               A                 1
Atlanta        JInc               A                 2
Atlanta        JInc               A                 3
Atlanta        JInc               A                 21
Atlanta        JInc               B                 2
Atlanta        JInc               B                 4
Atlanta        JInc               C                 2
Atlanta        JInc               C                 3
Atlanta        JInc               C                 5
Atlanta        JInc               C                 8
Atlanta        HInc               D                 9
Atlanta        HInc               D                 10
Atlanta        HInc               E                 11
Atlanta        HInc               E                 14
Atlanta        HInc               E                 12
Atlanta        HInc               E                 16
Atlanta        HInc               E                 17
Atlanta        HInc               E                 18
;
run;

proc iml;
use have;
read all var {Office Customer};
close have;

n=4; /*--Change it(the number of combination's members).*/
nrow=nrow(Office );
encoding=j(2,nrow,1);
encoding[1,]=0;

start function(x) global(Office,Customer,n);
count=ncol(unique(Customer[loc(x)]));
if ncol(unique(Office[loc(x)]))^=n then count=0;
return (count);
finish;

id=gasetup(2,nrow,1234);
call gasetobj(id,1,"function");
call gasetcro(id,0.95,2);
call gasetmut(id,0.05,3);
call gasetsel(id,100,1,1);
call gainit(id,10000,encoding);

niter = 100;
summary = j(niter,2);
mattrib summary [c = {"Max Count", "Avg Count"} l=""];
do i = 1 to niter;
call garegen(id);
call gagetval(value, id);
summary[i,1] = value[1];
summary[i,2] = value[:];
end;
call gagetmem(mem, value, id, 1);
Memebers=unique(Office[loc(mem)]);
print "Best Members:" Memebers[l=""],
"Max Count:   " value[l = ""] ;
call gaend(id);
quit;

```

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 11 replies
• 479 views
• 3 likes
• 2 in conversation