Order of Calculations

The COMPUTAB procedure provides alternative programming methods for performing most calculations. New column and row values are formed by adding values from the input data set, directly or with modification, into existing columns or rows. New columns can be formed in the input block or in column blocks. New rows can be formed in the input block or in row blocks.

This example illustrates the different ways to collect totals. Table 9.9 is the total sales report for two products, SALES1 and SALES2, during the years 1988–1990. The values for SALES1 and SALES2 in columns C88, C89, and C90 come from the input data set.

Table 9.9 Total Sales Report
 

C88

C89

C90

SALESTOT

SALES1

15

45

80

140

SALES2

30

40

50

120

YRTOT

45

85

130

260

The new column SALESTOT, which is the total sales for each product over three years, can be computed in several different ways:

  • in the input block by selecting SALESTOT for each observation:

          salestot = 1;
       
  • in a column block:

          coltot: salestot = c88 + c89 + c90;
    

In a similar fashion, the new row YRTOT, which is the total sales for each year, can be formed as follows:

  • in the input block:

          yrtot = sales1 + sales2;
    
  • in a row block:

          rowtot: yrtot = sales1 + sales2;
    

Performing some calculations in PROC COMPUTAB in different orders can yield different results, because many operations are not commutative. Be sure to perform calculations in the proper sequence. It might take several column and row blocks to produce the desired report values.

Notice that in the previous example, the grand total for all rows and columns is 260 and is the same whether it is calculated from row subtotals or column subtotals. It makes no difference in this case whether you compute the row block or the column block first.

However, consider the following example where a new column and a new row are formed:

Table 9.10 Report Sensitive to Order of Calculations
 

STORE1

STORE2

STORE3

MAX

PRODUCT1

12

13

27

27

PRODUCT2

11

15

14

15

TOTAL

23

28

41

?

The new column MAX contains the maximum value in each row, and the new row TOTAL contains the column totals. MAX is calculated in a column block:

   col: max = max(store1,store2,store3);

TOTAL is calculated in a row block:

   row: total = product1 + product2;

Notice that either of two values, 41 or 42, is possible for the element in column MAX and row TOTAL. If the row block is first, the value is the maximum of the column totals (41). If the column block is first, the value is the sum of the MAX values (42). Whether to compute a column block before a row block can be a critical decision.