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