Incorrect results might occur when you use a SAS data set that was the result of using the SORT procedure against a database management system (DBMS) table. The SORT procedure updates the header of the resulting SAS data set that has been sorted and verified. The incorrect results occur when the collating sequence that the DBMS uses to sort the data is different than the one used by SAS, and subsequent code uses the header information to generate results.

The sort information provided by the sort indicator is used internally for performance improvements. The following are several ways that the sort indicator might be used:

• The SORT procedure checks for the sort indicator before it sorts a data set so that that data is not re-sorted unnecessarily.
• The SQL procedure uses the sort indicator to process queries more efficiently and to determine whether an internal sort is necessary before performing a join operation.
• When using the sort indicator during index creation, SAS determines whether the data is already sorted by the key variable or variables in ascending order. It does this by checking the sort indicator in the data file. If the values in the sort indicator are in ascending order, SAS does not sort the values for the index file.
• When processing a WHERE expression without an index, SAS first checks the sort indicator. If the validated sort information is YES, SAS stops reading the file once there are no more values that satisfy the WHERE expression.
• If an index is selected for WHERE expression processing, the sort indicator for that data set is changed to reflect the order that is specified by the index.
• For BY-group processing, if the data set is already sorted by the BY variable, SAS does not use the index, even if the data set is indexed on the BY variable.
• If the validated sort information is set to YES, SAS does not need to perform another sort.

Here are some examples of cases in which the sort indicator might be incorrectly set:

• SAS is running on a UNIX or Windows host and the DBMS is located on a z/OS system. This results in the data being in EBCDIC sort order whereas SAS expects it to be in ASCII sort order. The EBCDIC character set is different from ASCII.
• The sort order of SAS is different from the DBMS. Some DBMSs sort without regard to the case of an alphanumeric value. Some sort nulls to the end of the sorted data while SAS places missing values before the rows with data.

To work around this issue, have SAS do the sorting.  This can be accomplished by setting the SORTPGM system option or doing a PROC SORT on the resulting data set after the data has been read into SAS.