BYSORT= LIBNAME Statement Option

Specifies the SPD Engine to perform an automatic sort when it encounters a BY statement.

Default:	YES
Interaction:	BYNOEQUALS= Data Set Option
Engine:	SPD Engine only

Syntax

Example 1: Group Formatting with BYSORT=YES by Default

Example 2: Using BYSORT=NO in the LIBNAME Statement

Syntax

BYSORT=YES | NO

Required Arguments

YES

specifies to automatically sort the data based on the BY variables when a BY statement is encountered instead of sorting the data ahead of time.

NO

specifies not to sort the data based on the BY variables. Specifying NO means that the data must already be sorted before the BY statement. Indexes are not used.

Details

DATA or PROC step processing using the default Base SAS engine requires that if there is no index or if the observations are not in order, the data set must be sorted before a BY statement is issued. In contrast, by default, the SPD Engine sorts the data returned to the application if the observations are not in order. Unlike PROC SORT, which creates a new sorted data set, the SPD Engine's automatic sort does not change the permanent data set and does not create a new data set. However, utility file space is used. For more information, see SPDEUTILLOC= System Option.

The default is BYSORT=YES. A BYSORT=YES argument enables the automatic sort, which outputs the observations in BY group order. If the data set option BYNOEQUALS=YES, then the observations within a group might be output in a different order from the order in the data set. Set BYNOEQUALS=NO to retain data set order.

The BYSORT=NO argument means that the data must already be sorted before the BY statement. Sorting can be from a previous PROC SORT or from the data set having been created in BY variable order. An error occurs if the data set is not sorted. When BYSORT=NO, grouped data is delivered to the application in data set order. Indexes are not used to retrieve the observations in BY variable order. The data set option BYNOEQUALS= has no effect when BYSORT=NO.

If you specify the BYSORT= option in the LIBNAME statement, it can be overridden by specifying BYSORT= in the PROC or DATA steps. Set BYSORT=YES in the DATA or PROC step, for input opens, to override BYSORT=NO in the LIBNAME statement. The point is that BYSORT=NO instructs the engine to do nothing to sort the data.

When you use the BYSORT=YES and the IDXWHERE= data set options, the following messages are written to the SAS log if you set the MSGLEVEL=I SAS system option:

If IDXWHERE=YES and there is an index on the BY variable, the index is used to order the rows of the table. The following message is written to the SAS log:
```
Note: BY ordering was produced by using an index for table tablename.
```
If IDXWHERE=NO or IDXWHERE=YES and there is no index on the BY variable, SPD Engine performs an automatic sort to order the rows of the table. The following message is written to the SAS log:
```
Note: BY ordering was produced by performing an automatic
sort on table tablename.
```

Comparisons

The BYSORT= data set option overrides the BYSORT= LIBNAME statement option.

Examples

Example 1: Group Formatting with BYSORT=YES by Default

libname growth spde 'SAS-library';
data growth.teens;
   input Name $ Sex $ Age Height Weight;
datalines;
Alfred M 14 69.0 112.5
Carol F 14 62.8 102.5
James M 13 57.3 83.0
Janet F 15 62.5 112.5
Judy F 14 64.3 90.0
Philip M 16 72.0 150.0
William M 15 66.5 112.0
;
proc print data=growth.teens; by sex;
run;

Even though the data was not sorted using PROC SORT, no error occurred because BYSORT=YES is the default. The output is shown:

Group Formatting with BYSORT=YES by Default

Example 2: Using BYSORT=NO in the LIBNAME Statement

In the following example, SAS returns an error because BYSORT=YES was not specified on the DATA or PROC steps to override the BYSORT=NO specification in the LIBNAME statement. Whenever automatic sorting is suppressed (BYSORT=NO), the data must be sorted on the BY variable before the BY statement (for example, by using PROC SORT).

libname growth spde 'SAS-library' bysort=no;
proc print data=growth.teens;
by sex;
run;

ERROR: Data set GROWTH.TEENS is not sorted in ascending sequence.
       The current by-group has Sex = M and the next by-group has Sex = F.
NOTE: The SAS System stopped processing this step because of errors.