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