Applying Summary Commands to Selected Fields in ACROSS Groups

When a request has multiple display fields and an ACROSS sort field, the report output has multiple columns under each ACROSS value. If you want to apply a summary field to some of the columns for each ACROSS value, but not others, you can specify the field names you want summarized. This technique is most useful for report requests that use the OVER phrase to place the fields on separate rows.

Reference: Usage Notes for ACROSS Summary Commands

Alphanumeric fields cannot be summarized.
With ACROSS, summary columns only display at the end of the ACROSS group (when the higher-level ACROSS field changes value).
SUMMARIZE and SUB-TOTAL propagate their calculations to each higher-level ACROSS field in the request.
Different operations from two ON phrases for the same sort break display in the same summary column, and allow a mixture of operations on summary columns.
If the same field is referenced in more than one ON phrase for the same sort break, the last summary command specified is applied.
You can specify a different summary operation for each sort break.
The SUMMARYLINES parameter does not affect processing for ACROSS fields.
When used with OVERs, the rows containing fields not to be summarized will be blank.
Prefix operators are supported on summary lines:
- The following prefix operators are supported for numeric fields: ASQ., AVE., CNT., FST., LST., MAX., MIN., SUM.
- Prefix operators PCT., RPCT., AND TOT. are not supported.
- Double prefix operators (such as PCT.CNT.) are not supported.
- The SUM. prefix operator produces the same summary values as a summary phrase with no prefix operator.
- SUMMARIZE and RECOMPUTE apply the calculations defined in the associated COMPUTE command to the summary values. Therefore, in order to perform the necessary calculations, the SUMMARIZE or RECOMPUTE command must specify all of the fields referenced in the COMPUTE command.
- If the same field has summary operations with different prefix operators at each level, the appropriate calculation is done at each level for the prefix operator specified.
- SUB-TOTAL and SUMMARIZE propagate their operations to all higher-level sort fields. If a request uses SUB-TOTAL or SUMMARIZE at multiple sort levels, more than one prefix operator may apply to the same field. When a SUB-TOTAL or SUMMARIZE command on a lower-level sort field propagates up to the higher levels, it applies its prefix operators only to those fields that did not already have a prefix operator specified at the higher level. For any field that had a prefix operator specified at a higher level, the original prefix operator is applied at the level at which it was first specified.
- Prefix operators on summary lines result in the same values whether the command is RECOMPUTE/SUMMARIZE or SUBTOTAL/SUB-TOTAL. For a computed field, the prefix operator is not applied, and the value is recalculated using the expression in the COMPUTE command and the values from the summary line.
- If an ACROSS field has an ACROSS-TOTAL phrase and a summary command with a prefix operator, the prefix operator is applied, not the ACROSS-TOTAL.

Top of page

Example: Subtotaling One Field Within an ACROSS Group

The following request against the GGSALES data source sums the DOLLARS and UNITS fields by CATEGORY and across REGION, but subtotals only the UNITS field.

TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'
  BY CATEGORY            
  ACROSS REGION SUBTOTAL UNITS
WHERE REGION EQ 'Midwest' OR 'West'
ON TABLE SET PAGE NOPAGE
END

The output shows that only the rows with the UNITS values are subtotaled.

                             Region
Category              Midwest      West         TOTAL
-------------------------------------------------------------
Coffee       Dollars   4178513      4473517
             Units      332777       356763       689540
Food         Dollars   4338271      4202337
             Units      341414       340234       681648
Gifts        Dollars   2883881      2977092
             Units      230854       235042       465896

Top of page

Example: Summarizing a Calculated Value in an ACROSS Group

The following request against the GGSALES data source sums the DOLLARS and UNITS fields and calculates DOLLARS PER UNIT across REGION. The request also has a higher-level ACROSS field, CATEGORY, so the SUMMARIZE command propagates to both ACROSS fields.

SET BYPANEL = ON
TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' UNITS AS 'Units'
AND COMPUTE DPERU/D9.2 = DOLLARS/UNITS;
  ACROSS CATEGORY
  ACROSS REGION
  ON REGION SUMMARIZE DPERU
  WHERE REGION EQ 'Midwest' OR 'West'
  WHERE CATEGORY EQ 'Food' OR 'Gifts'
  ON TABLE PCHOLD FORMAT PDF
END

The first panel of output shows:

The values of DOLLARS, UNITS, and DPERU for the Midwest and West regions under the Food category.
The summary column, which has a value just for the DPERU row. Note that for ACROSS, the summary column for REGION appears only after the higher-level ACROSS field, CATEGORY, changes value.
The values of DOLLARS, UNITS, and DPERU for the Midwest and West regions under the Gifts category.

PAGE   1.1
         Category
                    Food                                   Gifts
         Region
             Midwest      West         TOTAL        Midwest      West
-------------------------------------------------------------------------
Dollars      4338271      4202337                   2883881      2977092
Units         341414       340234                    230854       235042
DPERU          12.71        12.35        12.53        12.49        12.67

The second panel has the total column for the Gifts category and the grand total column. Each of those only has a value in the DPERU row.

 PAGE   1.2
         Category
                             TOTAL
         Region
                TOTAL
 ----------------------------------
 Dollars
 Units
 DPERU          12.58        12.55

Top of page

Example: Using Prefix Operators in a Summary Command With ACROSS

The following request against the GGSALES data source sums the DOLLARS and UNITS fields ACROSS CATEGORY and ACROSS REGION, with a SUMMARIZE command on the REGION field. The request also has a higher-level ACROSS field, CATEGORY, so the SUMMARIZE command propagates to both ACROSS fields. The SUMMARIZE command specifies the AVE. prefix operator for the DOLLARS field.

SET BYPANEL = ON
TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'
  ACROSS CATEGORY
  ACROSS REGION
  ON REGION SUMMARIZE AVE. DOLLARS
  WHERE REGION EQ 'Midwest' OR 'West'
  WHERE CATEGORY EQ 'Food' OR 'Gifts'
  ON TABLE PCHOLD FORMAT PDF
END

The first panel of output shows:

The values of DOLLARS and UNITS for the Midwest and West regions under the Food category.
The summary column, which has a value just for the DOLLARS row. Note that for ACROSS, the summary column for REGION appears only after the higher-level ACROSS field, CATEGORY, changes value.
The values of DOLLARS and UNITS for the Midwest and West regions under the Gifts category.

PAGE   1.1
         Category
                 Food                                   Gifts
         Region
           Midwest      West         TOTAL        Midwest      West
-------------------------------------------------------------------------
Dollars    4338271      4202337      4270304      2883881      2977092
Units       341414       340234                    230854       235042

The second panel has the total column for the Gifts category and the grand total column. Each of those only has a value in the DOLLARS row.

PAGE   1.2
        Category
                        TOTAL
        Region
           TOTAL
----------------------------------
Dollars    2930486      3600395
Units

Top of page

Example: Using Combinations of ACROSS Summary Commands

The following request against the GGSALES data source sums the DOLLARS and UNITS fields ACROSS CATEGORY and ACROSS REGION, with a SUMMARIZE command on the REGION field and a SUBTOTAL command on the CATEGORY field. The SUMMARIZE command specifies average DOLLARS and minimum UNITS. The SUBTOTAL command specifies minimum DOLLARS.

SET BYPANEL = ON
TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'
  ACROSS CATEGORY
  ACROSS REGION
    ON CATEGORY SUBTOTAL  MIN.DOLLARS
    ON REGION SUMMARIZE AVE.DOLLARS MIN.UNITS
  WHERE REGION EQ 'Midwest' OR 'West'
  WHERE CATEGORY EQ 'Food' OR 'Gifts'
END

On the output, all of the TOTAL columns have the minimum UNITS. The TOTAL columns associated with the REGION sort field have the average DOLLARS, but the TOTAL column associated with the CATEGORY sort field has the minimum DOLLARS because SUMMARIZE does not change the prefix operator associated with a higher-level sort field.

PAGE   1.1
         Category
                 Food                                   Gifts
         Region
           Midwest      West         TOTAL        Midwest      West
-------------------------------------------------------------------------
Dollars    4338271      4202337      4270304      2883881      2977092
Units       341414       340234       340234       230854       235042

PAGE   1.2
        Category
                        TOTAL
        Region
           TOTAL
----------------------------------
Dollars    2930486      2883881
Units       230854       230854