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

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

View solution in original post

5 REPLIES 5
Reeza
Super User
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
Fluorite | Level 6
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
Super User
It's possible there's a SQL solution, it depends on the data complexity.
Ksharp
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 723 views
  • 3 likes
  • 3 in conversation