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
========= ========= ========= =========
|