The COMPUTAB Procedure

Example 9.4 Consolidations

This example consolidates product tables by region and region tables by corporate division. Output 9.4.1 shows the North Central and Northeast regional summaries for the Equipment division for the first quarter. Output 9.4.2 shows the profit summary for the Equipment division. Similar tables for the Publishing division are produced but not shown here.

data product;
   input pcode div region month sold revenue recd cost;
datalines;
1 1 1 1 56 5600 29 2465
1 1 1 2 13 1300 30 2550
1 1 1 3 17 1700 65 5525
2 1 1 1  2  240 50 4900
2 1 1 2 82 9840 17 1666
1      1       1       1      37      3700     75    6375

   ... more lines ...   

proc format;
   value divfmt 1='Equipment'
                2='Publishing';
   value regfmt 1='North Central'
                2='Northeast'
                3='South'
                4='West';
run;

proc sort data=product;
   by div region pcode;
run;
title1 '     XYZ Development Corporation      ';
title2 ' Corporate Headquarters: New York, NY ';
title3 '           Profit Summary             ';
title4 '                                      ';

options linesize=96;
proc computab data=product sumonly;
   by div region pcode;
   sumby _total_ div region;

   format div    divfmt.;
   format region regfmt.;
   label  div = 'DIVISION';

   /* specify order of columns and column titles */
   columns jan feb mar qtr1 /
                     mtitle='- first quarter -' ' '  nozero;
   columns apr may jun qtr2 /
                     mtitle='- second quarter -' ' ' nozero;
   columns jul aug sep qtr3 /
                     mtitle='- third quarter -' ' '  nozero;
   columns oct nov dec qtr4 /
                     mtitle='- fourth quarter -' ' ' nozero;
   column  jan  / ' ' 'January' '=';
   column  feb  / ' ' 'February' '=';
   column  mar  / ' ' 'March' '=';
   column  qtr1 / 'Quarter' 'Summary' '=';

   column  apr  / ' ' 'April' '=' _page_;
   column  may  / ' ' 'May' '=';
   column  jun  / ' ' 'June' '=';
   column  qtr2 / 'Quarter' 'Summary' '=';

   column  jul  / ' ' 'July' '=' _page_;
   column  aug  / ' ' 'August' '=';
   column  sep  / ' ' 'September' '=';
   column  qtr3 / 'Quarter' 'Summary' '=';

   column  oct  / ' ' 'October' '=' _page_;
   column  nov  / ' ' 'November' '=';
   column  dec  / ' ' 'December' '=';
   column  qtr4 / 'Quarter' 'Summary' '=';

   /* specify order of rows and row titles */
   row     sold    / ' ' 'Number Sold' f=8.;
   row     revenue / ' ' 'Sales Revenue';
   row     recd    / ' ' 'Number Received' f=8.;
   row     cost    / ' ' 'Cost of' 'Items Received';
   row     profit  / ' ' 'Profit' 'Within Period' ol;
   row     pctmarg / ' ' 'Profit Margin' dul;

   /* select column for appropriate month */
   _col_ = month + ceil( month / 3 ) - 1;

   /* calculate quarterly summary columns */
   colcalc:
      qtr1 = jan + feb + mar;
      qtr2 = apr + may + jun;
      qtr3 = jul + aug + sep;
      qtr4 = oct + nov + dec;

   /* calculate profit rows */
    rowcalc:
       profit = revenue - cost;
       if cost > 0 then pctmarg = profit / cost * 100;
run;

Output 9.4.1: Summary by Regions for the Equipment Division

XYZ Development Corporation
Corporate Headquarters: New York, NY
Profit Summary
 

--------------------SUMMARY TABLE:  DIVISION=Equipment region=North Central-------------------- 
                                                                                                
                                   ------------- first quarter --------------                   
                                                                                                
                                                                      Quarter                   
                                     January   February      March    Summary                   
                                   =========  =========  =========  =========                   
                                                                                                
                  Number Sold            198        223        119        540                   
                                                                                                
                  Sales Revenue     22090.00   26830.00   14020.00   62940.00                   
                                                                                                
                  Number Received        255        217        210        682                   
                                                                                                
                  Cost of                                                                       
                  Items Received    24368.00   20104.00   19405.00   63877.00                   
                                   ---------  ---------  ---------  ---------                   
                                                                                                
                  Profit                                                                        
                  Within Period     -2278.00    6726.00   -5385.00    -937.00                   
                                                                                                
                  Profit Margin        -9.35      33.46     -27.75      -1.47                   
                                   =========  =========  =========  =========                   



XYZ Development Corporation
Corporate Headquarters: New York, NY
Profit Summary
 

----------------------SUMMARY TABLE:  DIVISION=Equipment region=Northeast---------------------- 
                                                                                                
                                   ------------- first quarter --------------                   
                                                                                                
                                                                      Quarter                   
                                     January   February      March    Summary                   
                                   =========  =========  =========  =========                   
                                                                                                
                  Number Sold             82        180        183        445                   
                                                                                                
                  Sales Revenue      9860.00   21330.00   21060.00   52250.00                   
                                                                                                
                  Number Received        162         67        124        353                   
                                                                                                
                  Cost of                                                                       
                  Items Received    16374.00    6325.00   12333.00   35032.00                   
                                   ---------  ---------  ---------  ---------                   
                                                                                                
                  Profit                                                                        
                  Within Period     -6514.00   15005.00    8727.00   17218.00                   
                                                                                                
                  Profit Margin       -39.78     237.23      70.76      49.15                   
                                   =========  =========  =========  =========                   


Output 9.4.2: Profit Summary for the Equipment Division

XYZ Development Corporation
Corporate Headquarters: New York, NY
Profit Summary
 

-------------------------------SUMMARY TABLE:  DIVISION=Equipment------------------------------ 
                                                                                                
                                   ------------- first quarter --------------                   
                                                                                                
                                                                      Quarter                   
                                     January   February      March    Summary                   
                                   =========  =========  =========  =========                   
                                                                                                
                  Number Sold            280        403        302        985                   
                                                                                                
                  Sales Revenue     31950.00   48160.00   35080.00  115190.00                   
                                                                                                
                  Number Received        417        284        334       1035                   
                                                                                                
                  Cost of                                                                       
                  Items Received    40742.00   26429.00   31738.00   98909.00                   
                                   ---------  ---------  ---------  ---------                   
                                                                                                
                  Profit                                                                        
                  Within Period     -8792.00   21731.00    3342.00   16281.00                   
                                                                                                
                  Profit Margin       -21.58      82.22      10.53      16.46                   
                                   =========  =========  =========  =========                   



Output 9.4.3 shows the consolidation report of profit summary over both divisions and regions.

Output 9.4.3: Profit Summary

XYZ Development Corporation
Corporate Headquarters: New York, NY
Profit Summary
 

-------------------------------------SUMMARY TABLE:  TOTALS------------------------------------ 
                                                                                                
                                   ------------- first quarter --------------                   
                                                                                                
                                                                      Quarter                   
                                     January   February      March    Summary                   
                                   =========  =========  =========  =========                   
                                                                                                
                  Number Sold            590        683        627       1900                   
                                                                                                
                  Sales Revenue     41790.00   55910.00   44800.00  142500.00                   
                                                                                                
                  Number Received        656        673        734       2063                   
                                                                                                
                  Cost of                                                                       
                  Items Received    46360.00   35359.00   40124.00  121843.00                   
                                   ---------  ---------  ---------  ---------                   
                                                                                                
                  Profit                                                                        
                  Within Period     -4570.00   20551.00    4676.00   20657.00                   
                                                                                                
                  Profit Margin        -9.86      58.12      11.65      16.95                   
                                   =========  =========  =========  =========