Bill of Material roll up

[dane7722]

I am trying to create a code that will give me all the top level parts from using a bill of Material table I have.  The table is set up similar to what I have shown below, basically I need to go from the very bottom to the top.  I am just not sure where to start, also the actual table will not be ordered where a whole bill of material is shown row after row. 

[Reeza]

Do you have a SAS/OR license?
You can check with

proc product_status;run;

If you see SAS/OR you can use the PROC BOM and anything without a 'parent' will be identified.
https://documentation.sas.com/?docsetId=orbomug&docsetTarget=orbomug_bom_examples01.htm&docsetVersio...

[dane7722]

Thanks for the help, unfortunately I don't have SAS/OR

[hashman · Accepted solution]

@dane7722:

You need 2 lookup tables, desirably with O(1) search run time: parent-to-child (PC) and child-to-parent (CP). Then:

1. Load the file into the tables.
2. Read the file, discarding all records where a child is also a parent, i.e. keep those where the children are not in PC table.
3. For each such record, repeatedly look in the CP table, every time reassigning the child value as a parent value.
4. When the reassigned parent value is no longer in CP table, you've found the top-level parent, so output.

In other (i.e. SAS) words (using hash tables for PC and CP):

data have ;                                                                                                                             
  input (parent child) (:$3.) ;                                                                                                         
  cards ;                                                                                                                               
1b1  1c1                                                                                                                                
1h1  1i1                                                                                                                                
1c1  1d1                                                                                                                                
1a1  1b1                                                                                                                                
1f1  1g1                                                                                                                                
1g1  1h1                                                                                                                                
1e1  1f1                                                                                                                                
;                                                                                                                                       
data want (rename=parent=top_level) ;                                                                                                   
  if _n_ = 1 then do ;                                                                                                                  
    dcl hash pc (dataset:"have") ;                                                                                                      
    pc.definekey ("parent") ;                                                                                                           
    pc.definedata ("child") ;                                                                                                           
    pc.definedone () ;                                                                                                                  
    dcl hash cp (dataset:"have") ;                                                                                                      
    cp.definekey ("child") ;                                                                                                            
    cp.definedata ("parent") ;                                                                                                          
    cp.definedone () ;                                                                                                                  
  end ;                                                                                                                                 
  set have ;                                                                                                                            
  if pc.check (key:child) ne 0 ;                                                                                                        
  do while (cp.find (key:parent) = 0) ;                                                                                                 
  end ;                                                                                                                                 
run ;               

Note that this code doesn't handle the entry errors making children the parents of themselves. If such a thing should happen, the DO loop above would iterate infinitely. To guard against this, you may want to put a limit on the number of iterations, such as:

  do _n_ = 1 to 100 while (cp.find (key:parent) = 0) ;                                                                                  
  end ; 

Kind regards

Paul D.

[dane7722]

@hashman:

   Thanks for your help!  

using these functions are very new to me.  but is there a way to set a where statement in here, to only run up the bill of material on a select few bottom level children parts . For example if I only wanted to know the top level of  1d1, and not 1i1.  There are just too many BOMs in my table to go through instance.

[hashman]

@dane7722: Sure. Just add your where clause beneath the SET statement, such as:

  set have ;                                                                                                                            
  where child in ("1d1") ;  

However, I think that it's better to generated the output unfiltered and then select from it the children you want. This is because the code itself identifies the true children-only, i.e. those who are not parents themselves.

Kind regards

Paul D. 

[Ksharp]

It is one to one match or one to many match ?

[dane7722]

@Ksharp
One to many match. Also still need a way to resolve only looking for a select few bottom levels. My table is about 30 million rows.

[Ksharp]

data have;
input _end  _start ;
cards;
2 1
3 2
4 2
5 2
6 3
10 6
7 6
8 7
9 7
;
run;
 
proc sql;
create table ancient 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  $ 200 ;
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 ancient;
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;

[dane7722]

@Ksharp 

Thank you

Is there any way to do this where not every path is created for example what if I only want to lookup paths for 2 and 10

which would make the output:

1|2

1|2|3|6|10

I don't need all paths from the source table.

[Ksharp]

It is not getting you all the pah, it give you the longest path.

If you only want the first and last part ,Change code as:

data have;
input _end  _start ;
cards;
2 1
3 2
4 2
5 2
6 3
10 6
7 6
8 7
9 7
;
run;
 
proc sql;
create table ancient as
select * from have 
 where _start not in (select _end from have);
quit;


data want(keep= first last); /*<------*/
if _n_ eq 1 then do;
length path _path  $ 200 ;
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 ancient;
length first last $ 80;  /*<-------*/
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 do;first=scan(path,1,'|');last=scan(path,-1,'|');output;end;  /*<-------*/
 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 do;first=scan(path,1,'|');last=scan(path,-1,'|');output;end;/*<------*/
  rc=ha.find_next();
end;
end;
pa.clear();
run;