SUMMARIZE command

Concept Information

Summarizing data

Groups records based on identical values in one or more character, numeric, or datetime fields. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

Syntax

SUMMARIZE {ON key_field <...n>|ON ALL <EXCLUDE field_name <...n>>} <SUBTOTAL numeric_field <...n>|SUBTOTAL ALL <EXCLUDE numeric_field <...n>>> <OTHER field <...n>|OTHER ALL <EXCLUDE field_name <...n>>> <TO {SCREEN|table_name|PRINT}> <LOCAL> <IF test> <WHILE test> <FIRST range|NEXT range> <PRESORT> <APPEND> <OPEN> <HEADER header_text> <FOOTER footer_text> <STATISTICS> <MODMEDQ> <STDEV> <CPERCENT> <ISOLOCALE locale_code>

Parameters

Name Description
ON key_field <...n> | ON ALL

One or more character, numeric, or datetime fields to summarize.

  • ON key_field use the specified field or fields

    Multiple fields must be separated by spaces, and can be different data types.

    If you summarize by more than one field, fields are summarized in the order that you list them. If you specify PRESORT, the nested sort of the output table follows the same order.

  • ON ALL use all fields in the table

    If you summarize by all fields, fields are summarized in the order that they appear in the table layout. If you specify PRESORT, the nested sort of the output table follows the same order.
EXCLUDE field_name

optional

Only valid when summarizing using ON ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune ON ALL, by excluding the specified fields.

EXCLUDE must immediately follow ON ALL. For example:

ON ALL EXCLUDE field_1 field_2
SUBTOTAL numeric_field <...n> | SUBTOTAL ALL

optional

One or more numeric fields or expressions to subtotal for each group.

Multiple fields must be separated by spaces. Specify ALL to subtotal all the numeric fields in the table.
EXCLUDE numeric_field

optional

Only valid when using SUBTOTAL ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune SUBTOTAL ALL, by excluding the specified fields.

EXCLUDE must immediately follow SUBTOTAL ALL. For example:

SUBTOTAL ALL EXCLUDE field_1 field_2
OTHER field <...n> | OTHER ALL

optional

One or more additional fields to include in the output.

  • OTHER field <...n> include the specified field or fields
  • OTHER ALL include all fields in the table that are not specified as key fields or subtotal fields

Use OTHER only with fields that contain the same value for all records in each summarized group. If you specify a field that contains values that are different for a summarized group, only the value for the first record in the group is displayed, which is not meaningful.

For example:

  • summarize a table on customer number an appropriate "other field" is Customer Name. Typically, the customer name is identical for all records with the same customer number.
  • summarize a vendor table by state an inappropriate "other field" is City. Only the first city listed for each state appears in the output. In this instance, the better approach is to summarize using both state and city as key fields, in that order.
EXCLUDE field_name

optional

Only valid when using OTHER ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune OTHER ALL, by excluding the specified fields.

EXCLUDE must immediately follow OTHER ALL. For example:

OTHER ALL EXCLUDE field_1 field_2
TO SCREEN table_name | PRINT

The location to send the results of the command to:

  • SCREEN displays the results in the Analytics display area

    Tip

    You can click any linked result value in the display area to drill down to the associated record or records in the source table.

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

  • PRINT sends the results to the default printer
LOCAL

optional

Saves the output file in the same location as the Analytics project.

Note

Applicable only when running the command against a server table with an output file that is an Analytics table.

The LOCAL parameter must immediately follow the TO parameter.
IF test

optional

A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition.

Note

The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test

optional

A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached.

Note

If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range | NEXT range

optional

The number of records to process:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

Use range to specify the number of records to process.

If you omit FIRST and NEXT, all records are processed by default.
PRESORT

optional

Sorts the table on the key field before executing the command.

Note

You cannot use PRESORT inside the GROUP command.

If you use PRESORT

If you use PRESORT, the output is sorted and contains a single, unique group for each set of identical values, or identical combination of values, in the key field or fields.

Tip

If the input table is already sorted, you can save processing time by not specifying PRESORT.

If you do not use PRESORT

If you do not use PRESORT, the output results use the sort order of the input table.

If the key field or fields contain non-sequential identical values, the output results contain more than one group for each set of identical values, or identical combination of values.

Note

Depending on the context, more than one group for each set of identical values, or identical combination of values, can defeat the purpose of summarizing.
APPEND

optional

Appends the command output to the end of an existing file instead of overwriting it.

Note

You must ensure that the structure of the command output and the existing file are identical:

  • the same fields
  • the same field order
  • matching fields are the same length
  • matching fields are the same data type

Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result.
OPEN

optional

Opens the table created by the command after the command executes. Only valid if the command creates an output table.
HEADER header_text

optional

The text to insert at the top of each page of a report.

header_text must be specified as a quoted string. The value overrides the Analytics HEADER system variable.
FOOTER footer_text

optional

The text to insert at the bottom of each page of a report.

footer_text must be specified as a quoted string. The value overrides the Analytics FOOTER system variable.
STATISTICS

optional

Note

Cannot be used unless SUBTOTAL is also specified.

Calculates average, minimum, and maximum values for all SUBTOTAL fields.
MODMEDQ

optional

Note

Cannot be used unless SUBTOTAL is also specified.

Calculates mode, median, first quartile, and third quartile values for all SUBTOTAL fields.
STDEV

optional

Note

Cannot be used unless SUBTOTAL is also specified.

Calculates standard deviation and percentage of total for all SUBTOTAL fields.
CPERCENT

optional

Calculates percentage of record count for each group.
ISOLOCALE

optional

Note

Applicable in the Unicode edition of Analytics only.

The system locale in the format language_country. For example, to use Canadian French, enter fr_ca.

Use the following codes:

  • language ISO 639 standard language code
  • country ISO 3166 standard country code

    If you do not specify a country code, the default country for the language is used.

If you do not use ISOLOCALE, the default system locale is used.

Examples

Total transaction amount per customer

You summarize an accounts receivable table on the Customer_Number field, and subtotal the Trans_Amount field. The output is grouped by customer and includes the total transaction amount for each customer:

OPEN Ar
SUMMARIZE ON Customer_Number SUBTOTAL Trans_Amount TO "Customer_total.FIL" PRESORT

Total transaction amount per customer per transaction date

You summarize an accounts receivable table on the Customer_Number and Trans_Date fields. You subtotal the Trans_Amount field.

The output is grouped by customer, and within customer by date, and includes the total transaction amount for each customer for each date the customer had transactions.

OPEN Ar
SUMMARIZE ON Customer_Number Trans_Date SUBTOTAL Trans_Amount TO "Customer_total_by_date.FIL" PRESORT

Total, average, minimum, and maximum transaction amounts per customer per transaction date

You add STATISTICS to the previous example.

In addition to the subtotaled transaction amount for each customer for each date the customer had transactions, you also calculate the average, minimum, and maximum transaction amounts for each customer for each date:

OPEN Ar
SUMMARIZE ON Customer_Number Trans_Date SUBTOTAL Trans_Amount TO "Customer_stats_by_date.FIL" PRESORT STATISTICS

Identical transaction amounts, same date

You summarize a credit card transactions table on the Trans_Date and Trans_Amount fields.

The output is grouped by date, and within date by amount. You can use the associated count to identify transactions with identical amounts and identical dates:

OPEN CC_Trans
SUMMARIZE ON Trans_Date Trans_Amount TO "Transactions_by_date_amount.FIL" OPEN PRESORT
SET FILTER TO COUNT > 1

Remarks

For more information about how this command works, see Summarizing data.

How it works

SUMMARIZE groups records that have the same value in a field, or the same combination of values across multiple fields. The output results contain a single record for each group, with a count of the number of records in the source table that belong to the group.

Subtotal and statistics: calculations and field names in the output results

You can use one or more optional parameters to perform statistical calculations on any SUBTOTAL field you specify. The statistical calculations are broken down by group in the output:

Optional Parameter Alternate column title (display name) in output table Field name in output table Calculation performed on subtotaled field
SUBTOTAL Total + subtotaled alternate column title subtotaled field name Subtotaled values for each group
STATISTICS Average + subtotaled alternate column title

a_subtotaled field name

The average value for each group

Minimum + subtotaled alternate column title

m_subtotaled field name

The minimum value for each group

Maximum + subtotaled alternate column title

x_subtotaled field name

The maximum value for each group
MODMEDQ

Median + subtotaled alternate column title

c_subtotaled field name

The median value for each group

  • Odd-numbered sets of values: the middle value
  • Even-numbered sets of values: the average of the two values at the middle

Mode + subtotaled alternate column title

o_subtotaled field name

The most frequently occurring value for each group

  • Displays "N/A" if no value occurs more than once
  • In the event of a tie, displays the lowest value

Q25 + subtotaled alternate column title

q_subtotaled field name

The first quartile value for each group (lower quartile value)

  • The result is an interpolated value based on an Analytics algorithm
  • Produces the same result as the QUARTILE and QUARTILE.INC functions in Microsoft Excel

Q75 + subtotaled alternate column title

p_subtotaled field name

The third quartile value for each group (upper quartile value)

  • The result is an interpolated value based on an Analytics algorithm
  • Produces the same result as the QUARTILE and QUARTILE.INC functions in Microsoft Excel
STDEV

STDDEV + subtotaled alternate column title

d_subtotaled field name

The standard deviation for each group

% Field + subtotaled alternate column title

f_subtotaled field name

Each group's subtotal expressed as a percentage of the field total
CPERCENT Percent of Count COUNT_PERCENTAGE The percentage of source table records belonging to each group

Note

Does not require a subtotal field