topic Re: How to write a SAS program to find relationship between nodes in SAS Programming

How to write a SAS program to find relationship between nodes

gyambqt — Mon, 26 Feb 2024 00:06:13 GMT

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

Re: How to write a SAS program to find relationship between nodes

Patrick — Mon, 26 Feb 2024 01:48:35 GMT

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

Re: How to write a SAS program to find relationship between nodes

Ksharp — Mon, 26 Feb 2024 06:30:13 GMT

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