BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
milts
Pyrite | Level 9

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	
;
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

8 REPLIES 8
Ksharp
Super User

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

milts
Pyrite | Level 9

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

FreelanceReinh
Jade | Level 19

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
Super User

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&colon;'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
Pyrite | Level 9

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
Super User

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&colon;'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
Pyrite | Level 9

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

milts
Pyrite | Level 9

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1691 views
  • 2 likes
  • 3 in conversation