I tried to implement FIFO (first in first out stack) concept to find all the child. User need to provide mother id in macro variable search_val The final desired result would come in work.output_list. %let search_val=2;
data input;
input C_cd M_cd;
datalines;
2 1
3 2
4 2
5 2
6 3
7 6
8 7
;
proc sql;
create table parent_child as
select a.c_cd as ID,
a.M_cd as mother, b.C_cd as child,
put(a.M_cd,8.) || ", "||put(a.c_cd,8.)|| ", "||put(b.C_cd ,8.)as list
from input a left join input b
on a.C_cd = b.M_cd;
quit;
%let cnt_val =1;
proc sql;
drop table fifo_list ;
drop table output_list ;
drop table new_list;
quit;
data fifo_list;
child=&search_val;
run;
%macro fifo;
%start: %put &cnt_val ;
data _null_;
set fifo_list (obs=1);
call symputx('val',child);
run;
proc sql;
delete from fifo_list where child=&val;
create table new_list as
select child from parent_child
where ID=&val and child <> . ;
quit;
proc append base=fifo_list data=new_list;run;
proc append base=output_list data=new_list;run;
proc sql;
select count(*) into :cnt_val
from fifo_list where child <> . ; ;
quit;
%if &cnt_val > 0 %then %goto start;
%mend;
%fifo;
... View more
