Specifying the Sort Order

In this section:

How to:

Sort field values are automatically displayed in ascending order, beginning with the lowest value and continuing to the highest. The default sorting sequence varies for operating systems. On z/OS it is a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields. On UNIX and Windows it is 0-9, A-Z, a-z for alphanumeric fields; 0-9 for numeric fields.

You have the option of overriding this default and displaying values in descending order, ranging from the highest value to the lowest value, by including HIGHEST in the sort phrase.

Syntax: How to Specify the Sort Order 

{BY|ACROSS} {LOWEST|HIGHEST} sortfield

where:

LOWEST

Sorts in ascending order, beginning with the lowest value and continuing to the highest value (a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields). This option is the default.

HIGHEST

Sorts in descending order, beginning with the highest value and continuing to the lowest value. You can also use TOP as a synonym for HIGHEST.

sortfield

Is the name of the sort field.

Example: Sorting in Ascending Order

The following report request does not specify a particular sorting order, and so, by default, it lists salaries ranging from the lowest to the highest. 

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY CURR_SAL
END

You can specify this same ascending order explicitly by including LOWEST in the sort phrase. 

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY LOWEST CURR_SAL
END

The output is:

Example: Sorting in Descending Order

The following request lists salaries ranging from the highest to lowest. 

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY HIGHEST CURR_SAL
END

The output is:

Specifying Your Own Sort Order

In this section:

How to:

Reference:

Sort field values are automatically displayed in ascending order, beginning with the lowest value and continuing to the highest.

You can override the default order and display values in your own user-defined sorting sequence. To do this, you need to decide the following:

  1. Which sort field values you want to allow. You can specify every sort field value, or a subset of values. When you issue your report request, only records containing those values are included in the report.
  2. The order in which you want the values to appear. You can specify any order. For example, you could specify that an A1 sort field containing a single-letter code be sorted in the order A, Z, B, C, Y, and so on.

There are two ways to specify your own sorting order, depending on whether you are sorting rows with BY, or sorting columns with ACROSS:

Syntax: How to Define Your Own Sort Order 

BY sortfield AS 'coltitle' ROWS value1 [AS 'text1'] 
OVER value2 [AS 'text2']
[... OVER valuen [ AS 'textn']]
END

where:

sortfield

Is the last BY field in the report.

coltitle

Is the column title for the BY field on the report output.

value1

Is the sort field value that is first in the sorting sequence.

AS 'text1'

Enables you to assign alternate text for the first row, which replaces the field value in the output. Enclose the text in single quotation marks.

value2

Is the sort field value that is second in the sorting sequence.

AS 'text2'

Enables you to assign alternate text for the second row, which replaces the field value in the output. Enclose the text in single quotation marks.

valuen

Is the sort field value that is last in the sorting sequence.

AS 'textn'

Enables you to assign alternate text for the last row, which replaces the field value in the output. Enclose the text in single quotation marks.

An alternative syntax is

FOR sortfield
value1 OVER value2 [... OVER valuen]

which uses the row-based reporting phrase FOR, described in Creating Financial Reports With Financial Modeling Language (FML).

Reference: Usage Notes for Defining Your Sort Order

  • Any sort field value that you do not specify in the BY ROWS OVER phrase is not included in the sorting sequence, and does not appear in the report.
  • Sort field values that contain embedded blank spaces should be enclosed in single quotation marks (').
  • Any sort field value that you do specify in the BY ROWS OVER phrase is included in the report, whether or not there is data.
  • If missing data is included in the report, it must be inserted at the lowest sort level.
  • The name of the sort field is not included in the report.
  • Each report request can contain only one BY ROWS OVER phrase. BY ROWS OVER is not supported with the FOR phrase. For information about the FOR phrase, see Creating Financial Reports With Financial Modeling Language (FML).

Example: Defining Your Row Sort Order

The following illustrates how to sort employees by the banks at which their paychecks are automatically deposited, and how to define your own label in the sorting sequence for the bank field. 

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY BANK_NAME ROWS 'BEST BANK' OVER STATE
   OVER ASSOCIATED OVER 'BANK ASSOCIATION'
END

The output is:

Syntax: How to Define Column Sort Sequence 

ACROSS sortfield COLUMNS value1 AND value2 [... AND valuen]

where:

sortfield

Is the name of the sort field.

value1

Is the sort field value that is first in the sorting sequence.

value2

Is the sort field value that is second in the sorting sequence.

valuen

Is the sort field value that is last in the sorting sequence.

Reference: ACROSS COLUMNS AND phrase

  • Any sort field value that you do not specify in the ACROSS COLUMNS AND phrase is not included in the label within the sorting sequence, and does not appear in the report.
  • Sort field values that contain embedded blank spaces should be enclosed in single quotation marks.
  • Any sort field value that you do specify in the ACROSS COLUMNS AND phrase is included in the report, whether or not there is data.
  • When using a COMPUTE with an ACROSS COLUMNS phrase, the COLUMNS should be specified last:
    ACROSS acrossfield [AND] COMPUTE compute_expression; COLUMNS values
  • Each report request may contain only one ACROSS COLUMNS AND phrase.

Example: Defining Column Sort Sequence

The following illustrates how to sum employee salaries by the bank at which they are automatically deposited, and to define your own label within the sorting sequence for the bank field. 

TABLE FILE EMPLOYEE
SUM CURR_SAL
ACROSS BANK_NAME COLUMNS 'BEST BANK' AND STATE
   AND ASSOCIATED AND 'BANK ASSOCIATION'
END

The output is:

BANK_NAME                                                                    
BEST BANK          STATE              ASSOCIATED         BANK ASSOCIATION
-------------------------------------------------------------------------
     $29,700.00         $18,480.00         $64,742.00         $27,062.00

Selecting and Assigning Column Titles to ACROSS Values

How to:

Reference:

When you use the ACROSS COLUMNS phrase to select and order the columns that display on the report output for an ACROSS sort field, you can assign each selected column a new column title using an AS phrase.

Syntax: How to Assign Column Titles To ACROSS Values 

ACROSS sortfield [AS title]
 COLUMNS aval1 [AS val1title] [{AND|OR} aval2 [AS val2title] [... {AND|OR} avaln [AS valntitle]]]

where:

sortfield

Is the ACROSS field name.

title

Is the title for the ACROSS field name.

AND|OR

Is required to separate the selected ACROSS values. AND and OR are synonyms for this purpose.

aval1, aval2, ... avaln

Are the selected ACROSS values to display on the report output.

val1title, val2title ...valntitle

Are the column titles for the selected ACROSS values.

Reference: Usage Notes for Assigning Column Titles to ACROSS Values

  • Any value you specify as an ACROSS value in the sort phrase will appear on the report output, even if the value is screened out by an IF or WHERE test, or if the value does not exist at all in the data source.

    Note: For styled output formats, SET HIDENULLACRS=ON removes empty columns in ACROSS groups from the report output.

  • Column titles for ACROSS fields appear on a single line of the report output.
  • Support for AS names for ACROSS values is limited to the TABLE FILE command.
  • When you create a HOLD file with SET ASNAMES = ON, the original field name is propagated to the output Master File, not the AS name.
Example: Selecting and Assigning Column Titles to ACROSS Values

The following request against the GGSALES data source selects the columns Coffee Grinder, Latte, and Coffee Pot for the ACROSS field PRODUCT, and assigns each of them a new column title: 

 TABLE FILE GGSALES
 SUM
 DOLLARS/I8M AS ''
 BY REGION
 ACROSS PRODUCT  AS 'Products'
   COLUMNS 'Coffee Grinder' AS 'Grinder'
    OR Latte AS 'caffellatte'
    AND 'Coffee Pot' AS 'Carafe'
ON TABLE SET PAGE NOPAGE
END

The output is:

             Products
             Grinder          caffellatte      Carafe
Region
----------------------------------------------------------------
Midwest         $666,622       $2,883,566         $599,878
Northeast       $509,200       $2,808,855         $590,780
Southeast       $656,957       $2,637,562         $645,303
West            $603,436       $2,670,405         $613,624