Performing Calculations at Sort Field Breaks

How to:

Reference:

You can use the RECAP and COMPUTE commands to create subtotal values in a calculation. The subtotal values are not displayed. Only the result of the calculation is shown on the report.

Syntax: How to Use Subtotals in Calculations

Both the RECAP and COMPUTE commands have similar syntax to other total and subtotal commands.

{BY|ON} fieldname1 {RECAP|COMPUTE} fieldname2[/format] = expression;
                                      [WHEN expression;]

where:

fieldname1

Is the field in the BY phrase. Each time the BY field changes value, a new recap value is calculated.

fieldname2

Is the field name that contains the result of the expression.

/format

Can be any valid format. The default is D12.2.

expression

Can be any valid expression, as described in Using Expressions. You must end the expression with a semicolon.

WHEN expression

Is for use with RECAP only. It specifies the conditional display of RECAP lines as determined by a Boolean expression (see Conditionally Displaying Summary Lines and Text). You must end the expression with a semicolon.

Reference: Usage Notes for RECAP and COMPUTE

  • RECAP uses the current value of the named sort field, the current subtotal values of any computational fields that appear as display fields, or the last value for alphanumeric fields.
  • RECAP reserves space at the bottom of the page to ensure that a RECAP will not be alone at the top of the next page while the data it is recapping is on the previous page. The same technique is used for subtotals and grand totals, but not for subfootings or COMPUTEs.
  • The field names in the expression must be fields that appear on the report. That is, they must be display fields or sort control fields.
  • Each RECAP value displays on a separate line. However, if the request contains a RECAP command and SUBFOOT text, the RECAP value displays only in the SUBFOOT text and must be specified in the text using a spot marker. (For details, see Using Headings, Footings, Titles, and Labels.)
  • The calculations in a RECAP or COMPUTE can appear anywhere under the control break, along with any text. (For details, see Using Headings, Footings, Titles, and Labels.)
  • In an ON phrase, a COMPUTE command is the same as a RECAP command.
  • The limit for ON sortfield RECAP phrases is 64 for each sort field.
  • You can specify multiple recap calculations in one RECAP phrase. Use the following syntax:
    ON sortfield RECAP field1/format= ... ;field2/format= ... ;
    .
    .
    .

Example: Using RECAP

The following request illustrates the use of RECAP (DEPT_NET) to determine net earnings for each department:

TABLE FILE EMPLOYEE
SUM DED_AMT AND GROSS
BY DEPARTMENT BY PAY_DATE
ON DEPARTMENT RECAP DEPT_NET/D8.2M = GROSS-DED_AMT;
WHEN PAY_DATE GT 820101
END

The output is:

DEPARTMENT  PAY_DATE          DED_AMT            GROSS
----------  --------          -------            -----
MIS         81/11/30        $1,406.79        $2,147.75
            81/12/31        $1,406.79        $2,147.75
            82/01/29        $1,740.89        $3,247.75
            82/02/26        $1,740.89        $3,247.75
            82/03/31        $1,740.89        $3,247.75
            82/04/30        $3,386.73        $5,890.84
            82/05/28        $3,954.35        $6,649.50
            82/06/30        $4,117.03        $7,460.00
            82/07/30        $4,117.03        $7,460.00
            82/08/31        $4,575.72        $9,000.00

** DEPT_NET           $22,311.98

PRODUCTION  81/11/30          $141.66          $833.33
            81/12/31          $141.66          $833.33
            82/01/29        $1,560.09        $3,705.84
            82/02/26        $2,061.69        $4,959.84
            82/03/31        $2,061.69        $4,959.84
            82/04/30        $2,061.69        $4,959.84
            82/05/28        $3,483.88        $7,048.84
            82/06/30        $3,483.88        $7,048.84
            82/07/30        $3,483.88        $7,048.84
            82/08/31        $4,911.12        $9,523.84

** DEPT_NET           $27,531.14

Example: Using Multiple RECAP Commands

You can include multiple RECAP or COMPUTE commands in a request. This option enables you to perform different calculations at different control breaks.

The following request illustrates the use of multiple RECAP commands.

TABLE FILE SALES
SUM UNIT_SOLD AND RETURNS
WHERE AREA EQ 'U' 
BY DATE BY AREA BY PROD_CODE
ON DATE RECAP
DATE_RATIO=RETURNS/UNIT_SOLD;
ON AREA UNDER-LINE RECAP
AREA_RATIO=RETURNS/UNIT_SOLD;
END

The output is:

DATE   AREA  PROD_CODE  UNIT_SOLD  RETURNS
----   ----  ---------  ---------  -------
10/17  U     B10               30        2
             B17               20        2
             B20               15        0
             C17               12        0
             D12               20        3
             E1                30        4
             E3                35        4

** AREA_RATIO                      .09    

** DATE_RATIO                      .09    

------------------------------------------
10/18  U     B10               13        1

** AREA_RATIO                      .08    

** DATE_RATIO                      .08    

------------------------------------------
10/19  U     B12               29        1

** AREA_RATIO                      .03    

** DATE_RATIO                      .03    

------------------------------------------