How to write a SAS program to find relationship between nodes

[gyambqt]

I have two tables:

Table1 and Table2

Data in Table1 can be like:

Table_a      Table_b

A                  B
A                  C
C                  E
B                   D

Data in table2 can be like:

Table_name      Status
A                          Completed
B                           NOTCOMPLETED
C                          COMPLETED
D                          NOTCOMPLETED
E                              OK

my output should be like:
Table1   status1                Table2     status2                             table3 status3
A            Completed       B             NOTCOMPLETED           D                 NOTCOMPLETED
A           Completed         C              COMPLETED                 E                  OK

I want to write all relations in one single row

[Patrick]

Why do you call your variables Table_a and Table_b and not something like Parent and Child? The name Table... for a variable is rather confusing.

Are your networks only tree structures or could they also be circular? And if not only tree structures then what should happen in such cases?

Do you have one or multiple networks? (=one or multiple nodes without a parent in case of tree structures only?)

It's also worth to search the communities a bit because similar questions have been asked in the past.

Below three such discussions I found via a quick search (and I know there are more discussions with solutions around).
https://communities.sas.com/t5/SAS-Programming/Nodes-Links-Routes/m-p/80086 

https://communities.sas.com/t5/SAS-Programming/How-to-find-link-between-nodes/m-p/341819

https://communities.sas.com/t5/SAS-Programming/Find-last-child/m-p/113752 

[Ksharp]

 /**********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;