topic Re: Proc BOM base SQL in New SAS User

Proc BOM base SQL

TBarney — Fri, 12 Jul 2019 15:29:40 GMT

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.

Re: Proc BOM base SQL

Reeza — Fri, 12 Jul 2019 15:33:20 GMT

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.

Re: Proc BOM base SQL

TBarney — Fri, 12 Jul 2019 15:48:57 GMT

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.

Re: Proc BOM base SQL

Reeza — Fri, 12 Jul 2019 16:39:50 GMT

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

Re: Proc BOM base SQL

Ksharp — Sat, 13 Jul 2019 12:11:02 GMT

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;

Re: Proc BOM base SQL

Reeza — Sat, 13 Jul 2019 18:25:24 GMT

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.