Details see the following URL.
And here is the code I offered, Hope can help other people.
data have; infile cards delimiter='|'; /*from is parent, to is child*/ input from $ to $ ; cards; 2 | 4 2 | 5 2 | 6 2 | 8 4 | 7 4 | 11 6 | 9 6 | 10 6 | 12 10 | 15 10 | 16 13 | 14 16 | 17 99|2 7|4 100|20 ; 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: 16); _ha.definekey('key'); _ha.definedone(); if 0 then set full; declare hash from_to(dataset:'full',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;
Xia Keshan
Hi, I tried executing this logic. But its showing error of duplicate key and also, it gives output of same node against multiple households.
The problem is here :
declare hash from_to(dataset:'full',hashexp:20,multidata:'y');
Should be
declare hash from_to(dataset:'full',hashexp:20,multidata:'y');
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.