ACCESS Procedure Reference

SORT Clause in a View Descriptor

When you define a view descriptor, you can also include a SORT clause to specify data order. You can reference only the CA-Datacom/DB fields selected for the view descriptor.

Without a SORT clause or a SAS BY statement, the data order is determined by the Native Key for the CA-Datacom/DB table (or by the Default Key specified in the access or view descriptor).

A SAS BY statement automatically issues a SORT clause to CA-Datacom/DB. However, the SAS BY statement might cause grouping of the output results in some procedures; this might not be what you want.

If a view descriptor already contains a SORT clause, the BY statement overrides the SORT clause for that program. An exception is when the SAS procedure includes the NOTSORTED option. Then, the SAS BY statement is ignored, and the view descriptor SORT clause is used.

View SORT Clause Syntax

The syntax for the SORT clause is

SORT field-name <ASCENDING|UP|A> <DESCENDING|DOWN|D> 
   <,field-name...>

The elements of the SORT clause are described here.

field-name: is a CA-Datacom/DB field name or SAS column name of a CA-Datacom/DB field included in the view descriptor. Use commas to separate sort keys. You can also specify either ascending or descending order for each field name.
ASCENDING|UP|A: specifies that you want the data ordered by ascending values of the field-name. ASCENDING is the default.
DESCENDING|DOWN|D: specifies that you want the data ordered by descending values of the field-name.

If you specify more than one CA-Datacom/DB field, the values are ordered by the first named field, then the second, and so on.

View SORT Clause Example

The following SORT clause causes the values to be presented in ascending order based on the values in field STATE, then within states in descending order based on the values in field CITY:

sort state, city down

View SORT Clause Guidelines

Consider the following guidelines when you specify a SORT clause in the view descriptor:

You can enter a WHERE clause or a SORT clause or both, in either order. But if you enter both, do not use a terminator between them.
The keyword WHERE is not required unless the WHERE clause is the second clause (following the SORT clause). The SORT clause must begin with SORT.
If you specify a SAS BY clause when you execute a procedure, it replaces the SORT clause in the view descriptor. However, if the SAS procedure includes the NOTSORTED option, the SAS BY clause is ignored and the SORT clause in the view descriptor is used. A message is written to the LOG window when the NOTSORTED option causes a SORT clause to be ignored.
The CA-Datacom/DB fields must be selected in the view descriptor in order for you to use them in the SORT clause.
In the SORT clause, you can specify multiple fields, separated by commas.
The SORT clause is not parsed (or checked) until the interface view engine tries to execute it for a procedure.
Field names in the SORT clause conditions can be SAS names or CA-Datacom/DB names. However, you should use SAS names for repeating fields or for fields within repeating fields.

Top of Page