/**********Search a tree **************/
data table1;
input Table_a $ Table_b $;
cards;
A B
A C
C E
B D
;
data table2;
input Table_name $ Status : $20.;
cards;
A Completed
B NOTCOMPLETED
C COMPLETED
D NOTCOMPLETED
E OK
;
data have;
set table1(rename=(Table_a=_start Table_b=_end));
run;
proc sql;
create table ancestor as
select * from have
where _start not in (select _end from have);
quit;
data want(keep=path);
if _n_ eq 1 then do;
length path _path $ 800 ;
if 0 then set have;
declare hash ha(hashexp:20,dataset:'have(where=(_start is not missing and _end is not missing))',multidata:'y');
ha.definekey('_start');
ha.definedata('_end');
ha.definedone();
declare hash pa(ordered:'y');
declare hiter hi_path('pa');
pa.definekey('n');
pa.definedata('n','path');
pa.definedone();
end;
set ancestor;
count=1;n=1;_n=1;
path=catx('|',_start,_end);
pa.add();
do while(hi_path.next()=0);
if n ne 1 then pa.remove(key:_n);_n=n;
_path=path;
_start=scan(path,-1,'|');
rc=ha.find();
if rc ne 0 then output;
do while(rc=0);
if not findw(path,strip(_end),'|') then do;
if length(path)+length(_end)+1 gt lengthc(path) then do;
putlog 'ERROR: The length of path and _path are set too short';
stop;
end;
count+1;n=count;
path=catx('|',path,_end);
pa.add();
path=_path;
end;
else output; /*it is a circle.*/
rc=ha.find_next();
end;
end;
pa.clear();
run;
data want2;
set want;
id+1;
length table $ 80;
do i=1 to countw(path,'|');
table=scan(path,i,'|');output;
end;
keep id table;
run;
data want3;
if _n_=1 then do;
if 0 then set want2;
if 0 then set table2;
declare hash h(dataset:'table2');
h.definekey('Table_name');
h.definedata('Status');
h.definedone();
end;
set want2;
call missing(Status);
rc=h.find(key:table);
drop rc Table_name;
run;
data want4;
set want3;
by id;
if first.id then n=0;
n+1;
run;
proc sql noprint;
select distinct catt('want4(where=(n=',n,') rename=(table=table',n,' status=status',n,'))') into :merge separated by ' '
from want4;
quit;
data want5;
merge &merge.;
by id;
drop n;
run;
... View more
