SPD Engine Data Set Options

BYSORT= Data Set Option

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

Valid in:	DATA step and PROC step
Affects data set option:	BYNOEQUALS=
Default:	YES

Syntax
Details
Examples
	Example 1: Group Formatting with BYSORT=YES by Default
	Example 2: BYSORT=NO

Syntax

BYSORT=YES | NO

YES: specifies to automatically sort the data based on the BY variables whenever a BY statement is encountered, instead of invoking PROC SORT before a BY statement.
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 on the specified BY variables. This result can be from a previous sort using PROC SORT, or from the data set having been created in BY variable order. 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. Therefore, you set BYSORT=NO in the LIBNAME statement and subsequently a BY statement is encountered. An error occurs unless your data has been sorted (either by previously using PROC SORT or because it was created in sorted order). Set BYSORT=YES in the DATA or PROC step, for input or update 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.
```

Examples

Example 1: Group Formatting with BYSORT=YES by Default

libname growth spde 'D:\SchoolAge'; 
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

                     The SAS System       

                         Sex=F 
         Obs    Name     Age    Height    Weight

          2     Carol     14     62.8      102.5
          4     Janet     15     62.5      112.5
          5     Judy      14     64.3       90.0


                         Sex=M 
         Obs     Name      Age    Height    Weight

          1     Alfred      14     69.0      112.5
          3     James       13     57.3       83.0
          6     Philip      16     72.0      150.0
          7     William     15     66.5      112.0

Example 2: BYSORT=NO

With BYSORT=NO in the PROC PRINT statement, SAS returns an error 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 'D:\SchoolAge';
proc print data=growth.teens (bysort=no); 
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.

Top of Page