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 ;
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;
Why would 4 and 6 go into another group ? because 7 < 8 ?
Yes, The id will go to the group where it has a higher score in it.
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.
So
node: 1->3->5->4
score : 8->10->7
7 less than 10 ,therefore drop 4 ,right ?
NOTE: change my code
multidata:'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;
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
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;
Thanks a lot! This really cut down my script from running for a day to a couple of minutes!
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.