Proc BOM base SQL

[TBarney]

We recently did some work for a customer using Proc BOM.  Now the customer is asking for the SQL so that they can do the same procedure on other packages on their own.  I have looked and have not been able to find a SQL version of the BOM procedure.  Any help, ideas, or pointers would be helpful.

[Reeza · Accepted solution]

There isn't one and BOM problems usually need recursion which SQL doesn't do well at all. There is a macro in the library here (sub_graph_macro) that uses hash tables to accomplish similar solutions - usually similar to traversing a tree - mathematically speaking.

[TBarney]

Thank you, the mathematician I am working with is aware of the macro, however the group asking the question is non-SAS and very SQL.
Thank you for the quick response.

[Reeza]

It's possible there's a SQL solution, it depends on the data complexity.

[Ksharp]

It depends on how many layer you want to search .

Here is an example.

data have;
input from to;
cards;
1 2
1 4
2 6
2 8
6 9
;

proc sql;
/*first layer*/
create table first as
 select a.*,b.to as to1
  from have as a left join have as b
   on a.to=b.from;

/*second layer*/
create table second as
 select a.*,b.to as to2
  from first as a left join have as b
   on a.to1=b.from;
quit;

[Reeza]

And if you know the number of layers ahead of time, which is often the actual issue...you don't know the depth of the tree ahead of time.