The COMPUTAB Procedure

Example 9.2 Enhancing a Report

The following example shows how a report can be enhanced from a simple listing to a complex report. The simplest COMPUTAB report is a transposed listing of the data in the SAS data set INCOMREP shown in Output 9.2.1. To produce this output, nothing is specified except the PROC COMPUTAB statement and a TITLE statement.

data incomrep;
   length type $ 8;
   input type :$8. date :monyy7.
         sales retdis tcos selling randd
         general admin deprec other taxes;
   format date monyy7.;
datalines;
BUDGET JAN1989 4600 300 2200 480 110 500 210 14 -8 510
BUDGET FEB1989 4700 330 2300 500 110 500 200 14  0 480
BUDGET MAR1989 4800 360 2600 500 120 600 250 15  2 520
ACTUAL JAN1989 4900 505 2100 430 130 410 200 14 -8 500
ACTUAL FEB1989 5100 480 2400 510 110 390 230 15  2 490
;
title 'Computab Report without Any Specifications';
proc computab data=incomrep;
run;

Output 9.2.1: Simple Report

Computab Report without Any Specifications

                      COL1       COL2       COL3       COL4       COL5          
                                                                                
        type        BUDGET     BUDGET     BUDGET     ACTUAL     ACTUAL          
        date       JAN1989    FEB1989    MAR1989    JAN1989    FEB1989          
        sales      4600.00    4700.00    4800.00    4900.00    5100.00          
        retdis      300.00     330.00     360.00     505.00     480.00          
        tcos       2200.00    2300.00    2600.00    2100.00    2400.00          
        selling     480.00     500.00     500.00     430.00     510.00          
        randd       110.00     110.00     120.00     130.00     110.00          
        general     500.00     500.00     600.00     410.00     390.00          
        admin       210.00     200.00     250.00     200.00     230.00          
        deprec       14.00      14.00      15.00      14.00      15.00          
        other        -8.00       0.00       2.00      -8.00       2.00          
        taxes       510.00     480.00     520.00     500.00     490.00          



To exclude the budgeted values from your report, select columns for ACTUAL observations only. To remove unwanted variables, specify the variables you want in a ROWS statement.

title 'Column Selection by Month';

proc computab data=incomrep;
   rows sales--other;
   columns jana feba mara;
   mnth = month(date);
   if type = 'ACTUAL';
      jana = mnth = 1;
      feba = mnth = 2;
      mara = mnth = 3;
run;

The report is shown in Output 9.2.2.

Output 9.2.2: Report That Uses Column Selection Techniques

Column Selection by Month

                                 JANA       FEBA       MARA                     
                                                                                
                   sales      4900.00    5100.00       0.00                     
                   retdis      505.00     480.00       0.00                     
                   tcos       2100.00    2400.00       0.00                     
                   selling     430.00     510.00       0.00                     
                   randd       130.00     110.00       0.00                     
                   general     410.00     390.00       0.00                     
                   admin       200.00     230.00       0.00                     
                   deprec       14.00      15.00       0.00                     
                   other        -8.00       2.00       0.00                     



To complete the report, compute new rows from existing rows. This is done in a row block (although it can also be done in the input block). Add a new column (QTR1) that accumulates all the actual data. The NOZERO option suppresses the zero column for March. The output produced by these statements is shown in Output 9.2.3.

proc computab data=incomrep;

   /* add a new column to be selected */
   /* qtr1 column will be selected several times */
   columns actual1-actual3 qtr1 / nozero;
   array collist[3] actual1-actual3;
   rows sales retdis netsales tcos grosspft selling randd general
        admin deprec operexp operinc other taxblinc taxes netincom;

   if type='ACTUAL';
   i = month(date);
   if i <= 3 then qtr1 = 1;
   collist[i]=1;

   rowcalc:
      if sales = . then return;
      netsales = sales - retdis;
      grosspft = netsales - tcos;
      operexp  = selling + randd + general + admin + deprec;
      operinc  = grosspft - operexp;
      taxblinc = operinc + other;
      netincom = taxblinc - taxes;
run;

Output 9.2.3: Report That Uses Techniques to Compute New Rows

Column Selection by Month

                               ACTUAL1    ACTUAL2       QTR1                    
                                                                                
                   SALES       4900.00    5100.00   10000.00                    
                   RETDIS       505.00     480.00     985.00                    
                   NETSALES    4395.00    4620.00    9015.00                    
                   TCOS        2100.00    2400.00    4500.00                    
                   GROSSPFT    2295.00    2220.00    4515.00                    
                   SELLING      430.00     510.00     940.00                    
                   RANDD        130.00     110.00     240.00                    
                   GENERAL      410.00     390.00     800.00                    
                   ADMIN        200.00     230.00     430.00                    
                   DEPREC        14.00      15.00      29.00                    
                   OPEREXP     1184.00    1255.00    2439.00                    
                   OPERINC     1111.00     965.00    2076.00                    
                   OTHER         -8.00       2.00      -6.00                    
                   TAXBLINC    1103.00     967.00    2070.00                    
                   TAXES        500.00     490.00     990.00                    
                   NETINCOM     603.00     477.00    1080.00                    



Now that you have all the numbers calculated, add specifications to improve the report’s appearance. Specify titles, row and column labels, and formats. The report produced by these statements is shown in Output 9.2.4.

/* now get the report to look the way you want it */
title  'Pro Forma Income Statement';
title2 'XYZ Computer Services, Inc.';
title3 'Period to Date Actual';
title4 'Amounts in Thousands';

proc computab data=incomrep;

   columns actual1-actual3 qtr1 /
            nozero f=comma7. +3 ' ';
   array collist[3] actual1-actual3;
   columns actual1 / 'Jan';
   columns actual2 / 'Feb';
   columns actual3 / 'Mar';
   columns qtr1 / 'Total' 'Qtr 1';
   rows sales    / ' '
                   'Gross Sales ';
   rows retdis   / 'Less Returns & Discounts';
   rows netsales / 'Net Sales'          +3 ol;
   rows tcos     / ' '
                   'Total Cost of Sales';
   rows grosspft / ' '
                   'Gross Profit';
   rows selling  / ' '
                   'Operating Expenses:'
                   '   Selling';
   rows randd    / '   R & D';
   rows general  / +3;
   rows admin    / '   Administrative';
   rows deprec   / '   Depreciation'       ul;
   rows operexp  / ' '                     skip;
   rows operinc  / 'Operating Income';
   rows other    / 'Other Income/-Expense' ul;
   rows taxblinc / 'Taxable Income';
   rows taxes    / 'Income Taxes'          ul;
   rows netincom / '   Net Income'         dul;

   if type = 'ACTUAL';
   i = month( date );
   collist[i] = 1;

   colcalc:
      qtr1 = actual1 + actual2 + actual3;

   rowcalc:
      if sales = . then return;
      netsales = sales - retdis;
      grosspft = netsales - tcos;
      operexp = selling + randd + general + admin + deprec;
      operinc = grosspft - operexp;
      taxblinc = operinc + other;
      netincom = taxblinc - taxes;
run;

Output 9.2.4: Specifying Titles, Row and Column Labels, and Formats

Pro Forma Income Statement
XYZ Computer Services, Inc.
Period to Date Actual
Amounts in Thousands

                                                                                
                                                                Total           
                                          Jan         Feb       Qtr 1           
                                                                                
         Gross Sales                    4,900       5,100      10,000           
         Less Returns & Discounts         505         480         985           
                                    ---------   ---------   ---------           
            Net Sales                   4,395       4,620       9,015           
                                                                                
         Total Cost of Sales            2,100       2,400       4,500           
                                                                                
         Gross Profit                   2,295       2,220       4,515           
                                                                                
         Operating Expenses:                                                    
            Selling                       430         510         940           
            R & D                         130         110         240           
            GENERAL                       410         390         800           
            Administrative                200         230         430           
            Depreciation                   14          15          29           
                                    ---------   ---------   ---------           
                                        1,184       1,255       2,439           
                                                                                
         Operating Income               1,111         965       2,076           
         Other Income/-Expense             -8           2          -6           
                                    ---------   ---------   ---------           
         Taxable Income                 1,103         967       2,070           
         Income Taxes                     500         490         990           
                                    ---------   ---------   ---------           
            Net Income                    603         477       1,080           
                                    =========   =========   =========