Summarizing Alphanumeric Columns

How to:

Include All Columns on Summary Lines

Reference:

Usage Notes for Summarizing Alphanumeric Columns

By default, subtotals (using the SUBTOTAL and SUB-TOTAL commands) and recalculations (using the RECOMPUTE and SUMMARIZE commands) only display values for numeric report columns. However, you can include alphanumeric columns on these summary lines by either specifying the columns you want to display on the summary lines or by using the asterisk wildcard character to display all fields on the summary lines.

The alphanumeric value displayed on a SUBTOTAL or SUB-TOTAL line is either the first, minimum, maximum, or last alphanumeric value within the sort group, depending on the value of the SUMPREFIX parameter. On a RECOMPUTE or SUMMARIZE line, alphanumeric values are recalculated using the summary values for that line.

Syntax: How to Include All Columns on Summary Lines

ON sortfield summarycommand *

where:

sortfield: Is the sort field for which a change in value triggers the summary line.
summarycommand: Is SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE.
*: Indicates that all fields, numeric and alphanumeric, should be included on the summary lines. You can either use the asterisk to display all columns or reference the specific columns you want to display.

Example: Including Alphanumeric Fields on Summary Lines

The following request against the GGSALES data source computes the alphanumeric equivalents of the DOLLARS and UNITS fields, creates an alphanumeric version of the formula for the ratio between DOLLARS and UNITS, and computes the numeric ratio between DOLLARS and UNITS. The RECOMPUTE * command recomputes all values on a change of value for the state sort field:

SET SUMPREFIX=FST
TABLE FILE GGSALES
SUM PRODUCT DOLLARS/I8M AS 'Dollars' IN 22 UNITS AS 'Units'
COMPUTE Formula/A19 = EDIT(DOLLARS)|'/'|EDIT(UNITS)|'=';
COMPUTE Ratio/F8    = DOLLARS/UNITS;
BY ST
BY CATEGORY NOPRINT
WHERE ST EQ 'CA' OR 'IL'
ON ST RECOMPUTE *
ON TABLE SET PAGE NOPAGE
END

On the output, the alphanumeric formula is recomputed using the summed numeric fields. However, the product value is taken from the first product within each sort value, as that field is not recomputed and SUMPREFIX=FST:

State  Product           Dollars     Units  Formula                 Ratio
-----  -------           -------     -----  -------                 -----
CA     Capuccino      $2,957,852    237246  02957852/00237246=         12
       Biscotti       $2,770,508    222844  02770508/00222844=         12
       Coffee Grinder $1,935,863    152276  01935863/00152276=         13

*TOTAL CA
       Capuccino      $7,664,223    612366  07664223/00612366=         13

IL     Espresso       $1,398,779    109581  01398779/00109581=         13
       Biscotti       $1,561,904    120976  01561904/00120976=         13
       Coffee Grinder $1,050,243     83541  01050243/00083541=         13

*TOTAL IL
       Espresso       $4,010,926    314098  04010926/00314098=         13


TOTAL  Capuccino     $11,675,149    926464  11675149/00926464=         13

Note that if the SUBTOTAL summary command had been used, the formula would not have been recomputed and would have displayed the values from the first line within each sort group.

Summarizing Alphanumeric Columns

Syntax: How to Include All Columns on Summary Lines

Example: Including Alphanumeric Fields on Summary Lines

Reference: Usage Notes for Summarizing Alphanumeric Columns