The BOM Procedure

In this example, the BOM procedure is used in a nonstandard application. Recall that the input data for the procedure requires items that are related in a parent-child hierarchy. The procedure uses this information to create an output data set that lists the items in a top-down order that can be used to aggregate information in a variety of ways. Suppose, for example, that the ABC Lamp Company has forecasts that are available for lamps to be stocked at various stores. The stores are served by warehouses that are, in turn, served by distribution centers. In other words, the different units of business are organized as a “tree.” If the company wishes to aggregate the forecasts to calculate the overall forecast at the distribution center, it can do so using the BOM procedure as follows.

/* The input data set */
data demand10;
format parent $14. child $14. code $2. ;
input parent & child & code $ pct nitems;
datalines;
Dist.Center 1  Warehouse 1    DC   1    .  
Dist.Center 1  Warehouse 2    DC   1    .  
Dist.Center 2  Warehouse 3    DC   1    .   
Dist.Center 2  Warehouse 4    DC   1    .   
Dist.Center 2  Warehouse 5    DC   1    .   
Warehouse 1    Store 01       WH   1    .   
Warehouse 1    Store 02       WH   1    .   
Warehouse 1    Store 03       WH   1    .   
Warehouse 2    Store 04       WH   1    .   
Warehouse 2    Store 05       WH   1    .    
Warehouse 3    Store 06       WH   1    .   
Warehouse 3    Store 07       WH   1    .   
Warehouse 3    Store 08       WH   1    .   
Warehouse 4    Store 09       WH   1    .   
Warehouse 4    Store 10       WH   .5   .   
Warehouse 5    Store 11       WH   1    .   
Warehouse 5    Store 10       WH   .5   .  
Store 01       .              ST   .   10  
Store 02       .              ST   .   20  
Store 03       .              ST   .   10  
Store 04       .              ST   .   20  
Store 05       .              ST   .   10  
Store 06       .              ST   .   20  
Store 07       .              ST   .   10  
Store 08       .              ST   .   20  
Store 09       .              ST   .   10  
Store 10       .              ST   .   20  
Store 11       .              ST   .   10  
;

The data set demand10 defines the parent-child relationships of the company’s business units. It also specifies the forecasts, via the nitems variable, for each store. The code variable specifies the type of the business unit identified by the parent variable: 'DC' for distribution center, 'WH' for warehouse, and 'ST' for store. Note that some business units may be stocked from more than one parent unit. The pct variable specifies the percentage of the items in the unit identified by the child variable that are from the parent unit. For example, 'Store 10' is stocked evenly (50 percent each) from 'Warehouse 4' and 'Warehouse 5'.

The following code constructs the hierarchical structure of the ABC Lamp Company and aggregates the forecasts for stores up to each distribution center. The aggregated forecasts for stores, warehouses, and distribution centers are displayed in Output 3.10.1, Output 3.10.2, and Output 3.10.3, respectively.

  /* Create the hierarchy structure */
proc bom data=demand10 out=indbom10;
   structure / part=parent
               parent=parent 
               component=child
               quantity=pct
               id=(nitems code);
run;

  /* Sort the output data set in reverse order */
proc sort data=indbom10;
   by descending part_id;
run;

  /* Aggregate forecasts through the hierarchy structure */
data accumulate10;
   set indbom10;
   array items[3] dcitems whitems stitems;
   retain dcitems whitems stitems 0;

   do i=1 to _level_;
      if nitems then items[i]=items[i] + pct * nitems;
   end;

   if nitems then items[_level_+1]=items[_level_+1] + nitems;

   output;

   do i=_level_+1 to 3;
     items[i]=0;
   end; 
run;

  /* Display the forecast for each store */
proc sort data=accumulate10(where=(code="ST")) 
          out=stores10
          nodupkey;
   by _part_;
run;

proc print data=stores10 noobs;
   title1 'ABC Lamp Company';
   title3 'Totals for Stores';
   var _Part_ code stitems;
run;

  /* Display the aggregated forecast for each warehouse */
proc sort data=accumulate10(where=(code="WH")) 
          out=houses10
          nodupkey;
   by _part_;
run;

proc print data=houses10 noobs;
   title1 'ABC Lamp Company';
   title3 'Totals for Warehouses';
   var _Part_ code whitems;
run;

  /* Display the aggregated forecast for each distribution center */
proc sort data=accumulate10(where=(code="DC")) 
          out=dcs10
          nodupkey;
   by _part_;
run;

proc print data=dcs10 noobs;
   title1 'ABC Lamp Company';
   title3 'Totals for Distribution Centers';
   format _Part_ $14. ;
   var _Part_ code dcitems;
run;