SORT Procedure: Windows

This version is a simplified version of the SORT procedure syntax. For the complete syntax and its explanation, see the SORT procedure in Base SAS Procedures Guide

The SORT procedure sorts observations in a SAS data set by one or more character or numeric variables, either replacing the original data set or creating a new, sorted data set. By default under Windows, the SORT procedure uses the ASCII collating sequence.

The SORT procedure uses the sort utility specified by the SORTPGM system option. Sorting can be done by SAS, your database, or the Windows SyncSort utility. You can use all the options available to the SAS sort utility, such as the SORTSEQ and NODUPKEY options. For a complete list of all options available, see the list of sort options in the See Also section.

Under Windows, you can use the SORTSIZE= option in the PROC SORT statement to limit the amount of memory that is available to the SORT procedure. This option might reduce the amount of swapping SAS must do to sort the data set. If PROC SORT needs more memory than you specify, it creates a temporary utility file to store the data in. The SORT procedure's algorithm can swap data more efficiently than Windows can.

The syntax of the SORTSIZE= option is as follows:

where memory-specification can be one of the following:

The default SAS configuration file sets this option to 256MB using the SORTSIZE= system option.

You can override the default value of the SORTSIZE= system option by specifying a different SORTSIZE= value in the PROC SORT statement, or by submitting an OPTIONS statement that sets the SORTSIZE= system option to a new value.

The TAGSORT option in the PROC SORT statement is useful in sorts when there might not be enough disk space to sort a large SAS data set. When you specify TAGSORT, the sort is a single-threaded sort. Do not specify TAGSORT if you want the SAS to use multiple threads to sort.

When you specify the TAGSORT option, only sort keys (that is, the variables specified in the BY statement) and the observation number for each observation are stored in the temporary files. The sort keys, together with the observation number, are referred to as tags. At the completion of the sorting process, the tags are used to retrieve the records from the input data set in sorted order. Thus, in cases where the total number of bytes of the sort keys is small compared with the length of the record, temporary disk use is reduced considerably. You should have enough disk space to hold another copy of the data (the output data set) or two copies of the tags, whichever is greater. Note that while using the TAGSORT option can reduce temporary disk use, the processing time can be much higher. However, on PCs with limited available disk space, the TAGSORT option can allow sorts to be performed in situations where they would otherwise not be possible.

If you want to provide your own collating sequences or change a collating sequence that has been provided for you, use the TRANTAB procedure to create or modify translate tables. For more information about the TRANTAB procedure, see SAS National Language Support (NLS): Reference Guide. When you create your own translate tables, they are stored in your Sasuser.Profile catalog and they override any translate tables by the same name that are stored in the HOST catalog.

If you want to see the names of the collating sequences stored in the HOST catalog (using the SAS Explorer), submit the following statement:

dm 'catalog sashelp.host' catalog;

Alternatively, you can select the View menu, select the Libraries item, then double-click on the Sashelp library, and then double-click on the HOST catalog. In batch mode, you can use the following statements to generate a list of the contents of the HOST catalog:

proc catalog catalog=sashelp.host;
   contents;
run;

Entries of type TRANTAB are the collating sequences.

If you want to see the contents of a particular translate table, use the following statements:

proc trantab table=table-name;
   list;
run;

The contents of the collating sequence are displayed in the SAS log.

If you have SyncSort installed at your site, you can use Syncsort as an alternative sorting algorithm to the database sort or the SAS sort. SAS determines which sort to use by the values that are set for the SORTPGM, SORTCUT, and SORTCUTP system options.

The SyncSort installation process adds the SyncSort directory to the Windows PATH statement. As long as the SyncSort directory is included in the Windows PATH statement, SAS is able to launch SyncSort. SyncSort is developed by Syncsort, Inc.

To always sort using the SyncSort sort routine, the value of the SORTPGM system option must be HOST. To set this option, submit the following OPTIONS statement:

options sortpgm=host;

-sortpgm host

The sort routine that SAS uses can be based on either the number of observations in a data set or on the size of the data set. When the SORTPGM option is set to BEST, SAS uses the first available and pertinent sorting algorithm based on this order of precedence:

If sorting is not to be done by the database, SAS looks at the values for the SORTCUT and SORTCUTP options to determine which sort to use.

SyncSort is used when the number of observations is greater than or equal to the value of sortcut. The SORTCUTP option specifies the number of bytes in the data set above which SyncSort is used.

If SORTCUT and SORTCUTP are set to zero, SAS uses the SAS sort routine. If you specify both options and either condition is met, SAS uses SyncSort.

When the following OPTIONS statement is in effect, the SyncSort routine is used when the number of observations is 501 or greater:

options sortpgm=best sortcut=500;

Here, the SyncSort routine is used when the size of the data set is greater than 40M:

options sortpgm=best sortcutp=40M;

For more information about these sort options, see SORTPGM System Option: Windows, SORTCUT System Option: Windows and SORTCUTP System Option: Windows.

By default, SyncSort uses the location that is specified in the -WORK option for temporary files. To change the location of SyncSort temporary files, specify a new location by using the SORTDEV option. Here is an example:

options sortdev="c:\temp\sortsync";

For more information about the SORTDEV options, see SORTDEV System Option: Windows .

Use the SORTANOM option to specify the options that you want to use for SyncSort:

Multiple options can be specified by concatenating the options:

options sortdev=btv;

For more information about the SORTANOM option, see SORTANOM System Option: Windows.

Use the SORTPARM option to pass Syncsort options to SyncSort. Enclose the options in quotations marks as in this OPTIONS statement:

options sortparm="SyncSort-options";

For information about the SORTPARM option, see SORTPARM System Option: Windows. See the SyncSort documentation for a description of the SyncSort options.

The SORTSEQ= option enables you to specify the collating sequence for your sort. For a list of valid values, see the SORT procedure in Base SAS Procedures Guide.

If your translation tables are not one-to-one mappings, then you can use one of the following methods to perform your sort:

The following code is an example of creating a view using a dummy BY variable:

options no date nostimer ls-78 ps-60;
options sortpgm=host msglevel=i;
data one;
   input name $ age;
   datalines;
   anne 35
   ALBERT 10
   JUAN 90
   janet 5
   bridget 23
   BRIAN 45
   ;
run;
data oneview / view=oneview;
   set one;
   name1=upcase(name);
run;
proc sort data=oneview out=final(drop=name1);
   by name1;
run;
proc print data=final;
run;

The output is the following:

  The SAS System
Obs        name       age
 1         ALBERT      10
 2         anne        35
 3         BRIAN       45
 4         bridget     23
 5         janet        5
 6         JUAN        90