topic Re: Getting the Clustured ID in SAS Programming

Getting the Clustured ID

Rahul_SAS — Tue, 11 Jul 2017 05:51:43 GMT

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

Please help.

Re: Getting the Clustured ID

Rahul_SAS — Tue, 11 Jul 2017 05:53:25 GMT

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

Re: Getting the Clustured ID

Ksharp — Tue, 11 Jul 2017 12:26:38 GMT

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;
 key=node;_ha.add();
 last=node;ha.add();
 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);
       key=to;_ha.add();
       count+1;
       last=to;ha.add();
      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;