Previous Page | Next Page

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

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




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.


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.


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:


Example 1: Group Formatting with BYSORT=YES by Default

libname growth spde 'D:\SchoolAge'; 
data growth.teens;
   input Name $ Sex $ Age Height Weight; 
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;

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       

         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

         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;

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.

Previous Page | Next Page | Top of Page