## removed

Frequent Contributor
Posts: 84

# removed

remove violating company policy

Super User
Posts: 8,127

## Re: Group Formation Dynamically - Macro need

Can you explain the purpose of this exercise?

Did you look at clustering procedures like FASTCLUS.

Super User
Posts: 23,778

## Re: Group Formation Dynamically - Macro need

Haven't looked at this in detail, but it sounds like a greedy algorithm and similar to propensity matching so perhaps look at what macro code is available for those methods?

Super User
Posts: 10,787

## Re: Group Formation Dynamically - Macro need

Branch is unique ?

Branch Count Status

16133 50 Group 1

28098 22 Group 1

35221 25 Group 2

62269 25 Group 2

71449 25 Group 2  <--- right ? should be 68135

54479 18 Group 3

55057 30 Group 3

68135 35 Group 3

78840 46 Group 4

86440 45 Group 4

```

data branch;
input branch y x ;
cards;
16133 41.276916 -80.083724
28098 35.270919 -81.108026
35221 33.454994 -86.895854
54479 44.756325 -90.297794
55057 44.445033 -93.181524
62269 38.562834 -89.896654
68135 41.207735 -96.175838
71449 31.564189 -93.481045
78840 29.367308 -100.890893
86440 34.908895 -114.541298
;
run;

data Branchcount;
input Branch Count;
cards;
16133 50
28098 22
35221 25
54479 18
55057 30
62269 25
68135 35
71449 25
78840 46
86440 45
;
run;

data branch_distance;
input br1 br2 distance;
cards;
54479 55057 143.9
55057 54479 143.9
68135 55057 270.4
55057 68135 270.4
35221 28098 353.7
28098 35221 353.7
62269 68135 380.3
68135 62269 380.3
68135 54479 385.6
54479 68135 385.6
62269 35221 390.3
35221 62269 390.3
35221 71449 406
71449 35221 406
28098 16133 418.1
16133 28098 418.1
62269 54479 428
54479 62269 428
62269 55057 440.3
55057 62269 440.3
78840 71449 467.3
71449 78840 467.3
71449 62269 523.5
62269 71449 523.5
28098 62269 536.8
62269 28098 536.8
62269 16133 553.6
16133 62269 553.6
16133 54479 570
54479 16133 570
35221 16133 656.6
16133 35221 656.6
68135 71449 681.7
71449 68135 681.7
16133 55057 699.4
55057 16133 699.4
68135 35221 738.9
35221 68135 738.9
28098 71449 758.8
71449 28098 758.8
54479 35221 800.7
35221 54479 800.7
54479 28098 815.1
28098 54479 815.1
35221 55057 829.8
55057 35221 829.8
68135 16133 837.2
16133 68135 837.2
68135 78840 858.5
78840 68135 858.5
35221 78840 872.9
78840 35221 872.9
86440 78840 885.8
78840 86440 885.8
55057 71449 888.7
71449 55057 888.7
62269 78840 893.5
78840 62269 893.5
28098 55057 899.8
55057 28098 899.8
28098 68135 914.7
68135 28098 914.7
54479 71449 926.3
71449 54479 926.3
16133 71449 1001
71449 16133 1001
68135 86440 1089.1
86440 68135 1089.1
55057 78840 1122.9
78840 55057 1122.9
78840 54479 1209.8
54479 78840 1209.8
78840 28098 1224.5
28098 78840 1224.5
86440 71449 1239.1
71449 86440 1239.1
55057 86440 1309.5
86440 55057 1309.5
62269 86440 1386.7
86440 62269 1386.7
78840 16133 1428.1
16133 78840 1428.1
86440 54479 1450
54479 86440 1450
35221 86440 1582.1
86440 35221 1582.1
86440 28098 1885.9
28098 86440 1885.9
86440 16133 1915.9
16133 86440 1915.9
;
run;

%let LB=70;
proc sort data=branch;by descending x;run;
data want;
if _n_ eq 1 then do;
if 0 then set Branchcount;
declare hash h_branch(dataset:'Branchcount');
h_branch.definekey('branch');
h_branch.definedata('count');
h_branch.definedone();

if 0 then set branch_distance;
declare hash h_distance(dataset:'branch_distance',multidata:'y');
h_distance.definekey('br1');
h_distance.definedata('br2','distance');
h_distance.definedone();

declare hash searched();
searched.definekey('branch');
searched.definedone();
end;
set branch;
if searched.check() ne 0 then do;
group+1;
br1=branch;     output;
do while(sum lt &LB );
rc=h_distance.find();min=999999;found=0;
do while(rc=0);
if searched.check(key:br2) ne 0 and distance lt min then do;
found=1;
min=distance;
branch=br2;
end;
rc=h_distance.find_next();
end;
if found then do;
h_branch.find();sum+count;
end;
else leave;
end;
end;
keep br1 group sum;
run;

```

Xia Keshan

Discussion stats
• 3 replies
• 356 views
• 2 likes
• 4 in conversation