The BOM Procedure

Example 3.8 Roll-Up Cost in Indented Bill of Material

As mentioned in the section Bill of Material Explosion and Implosion, the presence of the _Level_ variable and the order of the observations in the Indented BOM data set enables easy bill of material explosion and implosion. This example demonstrates how to calculate the costs for upper-level items using the SAS DATA step.

Suppose the ABC Lamp Company wishes to calculate the cost of the lamp 'LA01' based on the costs of purchased items that are at the "leaf" nodes of the BOM family tree. The Indented BOM data set can be used to aggregate values assigned at the "leaf" nodes, up the BOM family tree to calculate the value at the root node, or the final product 'LA01'.

In the following SAS code, the data set PMaster8 extends the part master file as displayed in Figure 3.1 to contain the cost for purchased items. PROC BOM is invoked with this Part Master data set and the Product Structure data set, ParComp0 as displayed in Figure 3.2 to create the Indented BOM data set, IndBOM8.

      /* Part master records */
   data PMaster8;
      format Part $8. Desc $24. Unit $8. Cost 8.2 ;
      input Part $ Desc & Unit $ Cost;
      datalines;
   1100    Finished shaft          Each         .  
   1200    6-Diameter steel plate  Each        9.25
   1300    Hub                     Each        5.00
   1400    1/4-20 Screw            Each        0.20
   1500    Steel holder            Each         .  
   1600    One-way socket          Each        3.50
   1700    Wiring assembly         Each         .
   2100    3/8 Steel tubing        Inches      0.05
   2200    16-Gauge lamp cord      Feet        0.35
   2300    Standard plug terminal  Each        0.50
   A100    Socket assembly         Each         .  
   B100    Base assembly           Each         .  
   LA01    Lamp LA                 Each         .  
   S100    Black shade             Each        4.10
   ;

  /* Create the indented BOM */
proc bom pmdata=PMaster8 data=ParComp0 out=IndBOM8;
   structure / part=Part
               parent=Parent 
               component=Component
               quantity=QtyPer 
               id=(Desc Unit Cost);
run;

The following code first reverses the order of the observations in the Indented BOM data set, and then determines the costs for the upper-level items. Afterward, the order of the observations is restored to the original order. The indented bill of material with cost information is displayed in Output 3.8.1. In each observation, the variable Cost contains the cost for the item identified by the value of the _Part_ variable.

  /* Sort the indented BOM in reverse order */
proc sort data=IndBOM8(drop=_Prod_ Paren_ID); 
   by descending Part_ID;
run;

  /* Roll up material cost */
data IndBOM8a;
   set IndBOM8;
   array costs[4] cost1 cost2 cost3 cost4;
   retain cost1 cost2 cost3 cost4 0;
   drop cost1 cost2 cost3 cost4;

   /* Determine the cost for the current item */ 
   if Cost=. then Cost=0;
   Cost = Cost + costs[_Level_+1];

   /* Roll up cost to the upper-level item */
   if _Level_ > 0 then 
      costs[_Level_]=costs[_Level_]+(QtyPer*Cost);

   /* Reset roll up cost for the current level */
   costs[_Level_+1]=0;

   output;
run;

  /* Sort the indented BOM back to the original order */
proc sort data=IndBOM8a; 
   by Part_ID;
run;

Output 3.8.1 Indented Bill of Material with Roll-Up Cost (IndBOM8a)

ABC Lamp Company

Indented Bill of Material, Part LA01

_Level_	_Parent_	_Part_	Desc	QtyPer	Qty_Prod	Unit	Cost
0		LA01	Lamp LA	.	1	Each	29.05
1	LA01	B100	Base assembly	1	1	Each	16.35
2	B100	1100	Finished shaft	1	1	Each	1.30
3	1100	2100	3/8 Steel tubing	26	26	Inches	0.05
2	B100	1200	6-Diameter steel plate	1	1	Each	9.25
2	B100	1300	Hub	1	1	Each	5.00
2	B100	1400	1/4-20 Screw	4	4	Each	0.20
1	LA01	S100	Black shade	1	1	Each	4.10
1	LA01	A100	Socket assembly	1	1	Each	8.60
2	A100	1500	Steel holder	1	1	Each	0.40
3	1500	1400	1/4-20 Screw	2	2	Each	0.20
2	A100	1600	One-way socket	1	1	Each	3.50
2	A100	1700	Wiring assembly	1	1	Each	4.70
3	1700	2200	16-Gauge lamp cord	12	12	Feet	0.35
3	1700	2300	Standard plug terminal	1	1	Each	0.50