STRATIFY command

Used to count the number of records falling into specified intervals or key fields, and totals one or more numeric fields for each stratum.

Syntax

STRATIFY <ON> numeric_field MINIMUM number <MAXIMUM number>{<INTERVALS n>|
FREE interval_value <...n> last_interval} <SUBTOTAL field <...n>> 
<KEY break_field> <SUPPRESS> <TO SCREEN|table_name|filename|GRAPH| PRINT>
<IF test> <{FIRST|NEXT} range> <WHILE test> <APPEND> <OPEN>
<HEADER header_text> <FOOTER footer_text> <LOCAL>

Parameters

ON numeric_field

Specifies the numeric field or expression to be stratified. The ON parameter can be omitted.

MINIMUM number

Specifies the minimum value of the first stratification interval.

MAXIMUM number

Specifies the maximum value of the last stratification interval. MAXIMUM is optional if you are using the FREE parameter, otherwise it is required.

INTERVALS n

Specifies the number of even intervals ACL produces over the range MINIMUM to MAXIMUM. The default is 10.

FREE interval_value <...n> last_interval

Specifies the start point of each interval and the end point of the last interval.

SUBTOTAL field

Specify one or more numeric fields to subtotal. Multiple fields must be separated by spaces.

KEY break_field

Optional. Specifies that a subtotal should be calculated each time the value of the specified field or expression changes. You can only specify a single field, but you can specify an expression that contains more than one field. The break_field value must be a character field or expression.

SUPPRESS

Optional. Specifies that values above the MAXIMUM value and below the MINIMUM value should be excluded from the command output.

TO SCREEN | table_name | filename | GRAPH | PRINT
  • Specify TO SCREEN to display the results on screen in ACL. This is the default output if the TO parameter is not specified.

  • Specify TO table_name to write the results to an ACL table. You must specify the table_name value as a quoted string with a .FIL file extension to create an ACL table.

    For example: TO "Output.FIL"

    You can also specify an absolute or relative file path to an existing folder to write the .FIL file to.

    For example: TO "C:\Output.FIL" or TO "Results\Output.FIL"

  • Specify TO filename to write the results to a file. You must specify the filename value as a quoted string with the appropriate file extension. For example, specify the .TXT file extension to create a text file.

    For example: TO "Output.TXT"

    You can also specify an absolute or relative file path to an existing folder to write the file to.

    For example: TO "C:\Output.TXT" or TO "Results\Output.TXT"

  • Specify TO PRINT to send the results to the default printer.

  • Specify TO GRAPH to create a graph of the command results and display it on screen in ACL.

IF test

Optional. Specifies a condition that must be met. The command is only executed on records that pass the test.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

WHILE test

Optional. Specifies that the command should only be executed while a condition is true. The command processing terminates as soon as the specified test evaluates as false, or the end of the table is reached.

APPEND

Optional. Specifies that command output should be appended to the end of an existing file instead of overwriting the existing file. You must ensure that the structure of the two files is identical when using this parameter, if maintaining a consistent file structure is important (e.g., when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

OPEN

Optional. Specifies that the table created by the command should be opened after the command executes. This parameter is only valid if the command creates an output table.

HEADER header_text

Optional. Specifies the text to insert at the top of each page of a report. The header_text value must be specified as a quoted string. The value overrides any value set for the ACL HEADER system variable.

FOOTER footer_text

Optional. Specifies the text to insert at the bottom of each page of a report. The footer_text value must be specified as a quoted string. The value overrides any value set for the ACL FOOTER system variable.

LOCAL

Optional. Specifies that the output file should be saved in the same location as the ACL project. This parameter only applies when the command is run on an ACL Server table and the output file is an ACL table.

Remarks

You can use the STRATIFY command to count the number of records falling into specified intervals (strata) of numeric field or expression values, as well as to subtotal one or more fields for each stratum. The record count for each stratum is listed in a COUNT column in the output.

You can run the STATISTICS command before running the STRATIFY command to automatically populate the MINIMUM and MAXIMUM parameter values.

Example

In the following example the results of the STRARIFY command are output to a new ACL table.

STRATIFY ON prodcls SUBTOTAL mktval MINIMUM 1 MAXIMUM 100 INTERVALS 10
TO "Stratify_Results.fil" 


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback