SUMMARIZE command
Concept Information
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.
|
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.
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:
|
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:
|
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:
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 PRESORTIf 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 PRESORTIf 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:
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:
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
|
Mode + subtotaled alternate column title |
o_subtotaled field name |
The most frequently occurring value for each group
|
|
Q25 + subtotaled alternate column title |
q_subtotaled field name |
The first quartile value for each group (lower quartile value)
|
|
Q75 + subtotaled alternate column title |
p_subtotaled field name |
The third quartile value for each group (upper quartile value)
|
|
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 |