topic Re: Bill of Material roll up in SAS Programming

Bill of Material roll up

dane7722 — Wed, 22 Jan 2020 17:15:15 GMT

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.

Re: Bill of Material roll up

Reeza — Wed, 22 Jan 2020 17:54:12 GMT

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&docsetVersion=14.3&locale=en

Re: Bill of Material roll up

dane7722 — Wed, 22 Jan 2020 18:08:50 GMT

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

Re: Bill of Material roll up

hashman — Wed, 22 Jan 2020 18:31:46 GMT

@dane7722:

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

Load the file into the tables.
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.
For each such record, repeatedly look in the CP table, every time reassigning the child value as a parent value.
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.

Re: Bill of Material roll up

dane7722 — Wed, 22 Jan 2020 20:09:47 GMT

@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.

Re: Bill of Material roll up

hashman — Wed, 22 Jan 2020 20:34:08 GMT

@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.

Re: Bill of Material roll up

Ksharp — Thu, 23 Jan 2020 13:04:58 GMT

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

Re: Bill of Material roll up

dane7722 — Thu, 23 Jan 2020 13:10:48 GMT

@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.

Re: Bill of Material roll up

Ksharp — Thu, 23 Jan 2020 13:50:07 GMT

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;

Re: Bill of Material roll up

dane7722 — Thu, 23 Jan 2020 14:54:10 GMT

@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.

Re: Bill of Material roll up

Ksharp — Sat, 25 Jan 2020 07:45:25 GMT

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;