Resources

Consolidations


/*--------------------------------------------------------------

                    SAS Sample Library

        Name: ctaex04.sas
 Description: Example program from SAS/ETS User's Guide,
              The COMPUTAB Procedure
       Title: Consolidations
     Product: SAS/ETS Software
        Keys: programmable tabular reports
        PROC: COMPUTAB
       Notes:

--------------------------------------------------------------*/

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
2      1       1       1      84     10080     28    2744
3      1       1       1      19      2470     73    7884
1      1       1       2       8       800     74    6290
2      1       1       2      71      8520     77    7546
3      1       1       2      49      6370     19    2052
1      1       1       3      16      1600     40    3400
2      1       1       3      46      5520     86    8428
3      1       1       3      40      5200     19    2052
1      1       2       1      17      1700     24    2040
2      1       2       1      29      3480     57    5586
3      1       2       1      36      4680     81    8748
1      1       2       2      62      6200     37    3145
2      1       2       2      21      2520      6     588
3      1       2       2      97     12610     24    2592
1      1       2       3      63      6300      3     255
2      1       2       3      84     10080     99    9702
3      1       2       3      36      4680     22    2376
4      2       1       1      42       840     14     224
5      2       1       1      75      2250      6     132
6      2       1       1      77      3080     79    2212
4      2       1       2      56      1120     16     256
5      2       1       2      32       960     64    1408
6      2       1       2      65      2600     84    2352
4      2       1       3      22       440     92    1472
5      2       1       3       3        90     87    1914
6      2       1       3      49      1960     56    1568
4      2       2       1      30       600     49     784
5      2       2       1      37      1110     47    1034
6      2       2       1      49      1960     44    1232
4      2       2       2      81      1620     85    1360
5      2       2       2      39      1170     61    1342
6      2       2       2       7       280     79    2212
4      2       2       3      95      1900     74    1184
5      2       2       3      91      2730     50    1100
6      2       2       3      65      2600     41    1148
;

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;