## Getting the Clustured ID

Frequent Contributor
Posts: 79

# Getting the Clustured ID

Hi All,

I have the data like

 ExposureId LimitId CollateralId 1 L1 C1 2 L1 C2 3 L2 C1 4 L2 C3 5 L3 C4 6 L3 C5 7 L4 C6 8 L5 C5

and want the output like

 ExposureId LimitId CollateralId 1 L1 C1 2 L1 C2 3 L2 C1 4 L2 C3 5 L3 C4 6 L3 C5 7 L4 C6 8 L5 C5

Frequent Contributor
Posts: 79

## Re: Getting the Clustured ID

ignore the previous output...i want output like..
ExposureId LimitId CollateralId ClusterId
1 L1 C1 1
2 L1 C2 1
3 L2 C1 1
4 L2 C3 1
5 L3 C4 2
6 L3 C5 2
7 L4 C6 3
8 L5 C5 2

Thank You
Super User
Posts: 10,787

## Re: Getting the Clustured ID

Assuming there are not misisng value in any one of limitid and collateralid.

``````data x;
input ExposureId LimitId \$ CollateralId \$ ;
cards;
1 L1 C1
2 L1 C2
3 L2 C1
4 L2 C3
5 L3 C4
6 L3 C5
7 L4 C6
8 L5 C5
;
run;
data have;
set x(rename=(LimitId=from CollateralId=to));
keep from to;
run;
data full;
set have end=last;
if _n_ eq 1 then do;
declare hash h();
h.definekey('node');
h.definedata('node');
h.definedone();
end;
output;
node=from; h.replace();
from=to; to=node;
output;
node=from; h.replace();
if last then h.output(dataset:'node');
drop node;
run;

data want(keep=node household);
declare hash ha(ordered:'a');
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('last');
ha.definedone();
declare hash _ha(hashexp: 20);
_ha.definekey('key');
_ha.definedone();

if 0 then set full;
declare hash from_to(dataset:'full(where=(from is not missing and to is not missing))',hashexp:20,multidata:'y');
from_to.definekey('from');
from_to.definedata('to');
from_to.definedone();

if 0 then set node;
declare hash no(dataset:'node');
declare hiter hi_no('no');
no.definekey('node');
no.definedata('node');
no.definedone();

do while(hi_no.next()=0);
household+1; output;
count=1;
rc=hi.first();
do while(rc=0);
from=last;rx=from_to.find();
do while(rx=0);
key=to;ry=_ha.check();
if ry ne 0 then do;
node=to;output;rr=no.remove(key:node);
count+1;
end;
rx=from_to.find_next();
end;
rc=hi.next();
end;
ha.clear();_ha.clear();
end;
stop;
run;

data final_want;
if _n_=1 then do;
if 0 then set want;
declare hash h(dataset:'want');
h.definekey('node');
h.definedata('household');
h.definedone();
end;
set x;
call missing(household);
rc=h.find(key:LimitId);
drop rc node;
run;``````
Discussion stats
• 2 replies
• 158 views
• 0 likes
• 2 in conversation