The BOM Procedure |
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;
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 |
Copyright © SAS Institute, Inc. All Rights Reserved.