🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Proc BOM base SQL

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Proc BOM base SQL

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.
5 REPLIES 5
Super User

## Re: Proc BOM base SQL

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.
Fluorite | Level 6

## Re: Proc BOM base SQL

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.
Super User

## Re: Proc BOM base SQL

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

## Re: Proc BOM base SQL

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;``````
Super User

## Re: Proc BOM base SQL

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.
Discussion stats
• 5 replies
• 722 views
• 3 likes
• 3 in conversation