Clustering Links

[milts]

I would like to seek your expert advice how do i attain the result table by using the source. I would want to group the ids which are linked together.

As you can see here id 5 is actually both linked to id 1 and 4, but the score of the link between 1 and 5 is higher. Reason that on the desired result it 5 will be under group 1

Greatly appreciate your inputs.

data source;
input id1 id2 score;	
datalines;
1	2	8
1	3	8
3	5	10
5	4	7
4	6	8
;
		
		
data result;		
input group	id;
datalines;
1	1	
1	2	
1	3	
1	5	
2	4	
2	6	
;

[Ksharp]

Why would 4 and 6 go into another group ? because 7 < 8 ?

[milts]

Yes, The id will go to the group where it has a higher score in it.

[FreelanceReinh]

Hello @milts,

Recently, I contributed some code to a discussion of a very similar question (except that no scores were involved there). Please see the thread Recursive lookup for ID's.

[Ksharp]

So 

 node: 1->3->5->4

score :    8->10->7 

7 less than 10 ,therefore drop 4 ,right ?

NOTE: change my code  

multidata&colon;'y'

into

m........ta : 'y' 

It seems this forum run into some problem.

UPDATED

data have;
infile cards expandtabs truncover ;
input from $ to $ score ;
cards;
1 2 8
1 3 8
3 5 10
5 4 7
4 6 8
;
run;




data _null_;
 if _n_ eq 1 then do;
 if 0 then set have(rename=(score=_score));
 declare hash k(dataset:'have(rename=(score=_score)');
 k.definekey('from');
 k.definedata('_score');
 k.definedone();
 
 declare hash h();
 h.definekey('drop');
 h.definedata('drop');
 h.definedone();
 end;
set have end=last; 
 _score=.;
 rc=k.find(key:to);
 if _score lt score and not missing(_score) then do;drop=to;h.replace();end;
 if last then h.output(dataset:'drop_obs');
run;
 
data full; 
 if _n_ eq 1 then do;
 if 0 then set drop_obs;
 declare hash k(dataset:'drop_obs');
 k.definekey('drop');
 k.definedone();
 
 declare hash h();
 h.definekey('node');
 h.definedata('node');
 h.definedone();
 end;
set have end=last;
if k.check(key:from) ne 0 then output;
 node=from; h.replace();
 from=to; to=node;
if k.check(key:to) ne 0 then output;
 node=from; h.replace();

 if last then h.output(dataset:'node');
 drop node drop score;
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;

[milts]

Cool! Couldn't thank you enough as this really helps a lot.

But let's say removing the score, and I just want to get the linkings how do i implement it using hash objects? 

So given the data sample links:

1->2

1->3

3->5

5->4

4->6

What portion of the code below should I tweak so that the result of this links is that they will fall under the same household?

Thanks in advance!

All wil 

[Ksharp · Accepted solution]

That would lead to a more simple question.

data have;
infile cards ;
input from $  to $ ;
cards;
1 2
1 3
3 5
5 4
4 6
;
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;

[milts]

Thanks a lot! This really cut down my script from running for a day to a couple of minutes!

[milts]

I guess you're example here works for me. Let me try this one out.

https://communities.sas.com/t5/Base-SAS-Programming/Recursive-lookup-for-ID-s/td-p/259635/page/3