Variables for Sorts

SPDSBSRT=

Use the SPDSBSRT= macro variable to configure SPD Server's sorting behavior when it encounters a BY-clause and there is no index available.

Syntax

SPDSBSRT=YES|NO

Default: YES

Corresponding Table Option:BYSORT=

Arguments

YES

SPD Server performs a server sort when it encounters a BY clause and there is no index available.

SPD Server does not perform a sort when it encounters a BY clause.

Description

Base SAS software requires an explicit PROC SORT statement to sort SAS data. In contrast, SPD Server sorts a table whenever it encounters a BY clause, if it determines that the table has no index.

Advantages for using SPD Server implicit sorts are discussed in detail in the Help section for Additional LIBNAME Options.

Example 1

At the start of a session to run old SAS programs, you realize that you do not have time to remove the existing PROC SORT statements. These statements are present only to generate print output.

To avoid redundant Server sorts, configure SPD Server to turn off implicit sorts. Put the macro variable assignment in your autoexec.sas file so SPD Server retains the configuration for all job sessions.

%let SPDSBSRT=NO;

During the Example 1 session, you decide to run a new program that has no PROC SORT statements. Instead, the new program takes advantage of SPD Server implicit sorts.

data inventory.old_autos;
   input
      year $4.
      @6 manufacturer $12.
      model $10.
      body_style $5.
      engine_liters
      @39 transmission_type $1.
      @41 exterior_color $10.
      options $10.
      mileage condition;

   datalines;

1971 Buick       Skylark   conv  5.8 A yellow  00000001 143000 2
1982 Ford        Fiesta    hatch 1.2 M silver  00000001  70000 3
1975 Lancia      Beta      2door 1.8 M dk blue 00000010  80000 4
1966 Oldsmobile  Toronado  2door 7.0 A black   11000010 110000 3
1969 Ford        Mustang   sptrf 7.1 M red     00000111 125000 3
;

PROC PRINT data=inventory.old_autos
; by model;

run;

When the code executes, the PRINT procedure returns an error message. What happened? SAS expected INVENTORY.OLDAUTOS to be sorted before it would generate print output. Since there is no PROC SORT statement -- and implicit sorts are still turned off -- the sort does not occur.

Example 2

Keep implicit sorts turned off for the session, but specify an implicit sort for the table INVENTORY.OLDAUTOS.

 PROC PRINT data=inventory.oldautos(bysort=yes);
 by model;
 run;

SPDSNBIX=

Use the SPDSNBIX= macro variable to configure whether to use an index during a BY-sort.

Syntax

SPDSNBIX=YES|NO

Default: NO

Corresponding Server Parameter Option: [NO]BYINDEX

Arguments

YES

Set SPDSNBIX=YES to suppress index use during a BY-sort. If the distribution of the values in the table are not relatively sorted or clustered, using the index for the BY sort can result in poor performance.

Set SPDSNBIX=NO or use the default value to allow the [NO]BYINDEX server parameter option to determine whether to use an index for a BY sort.

Example

%let SPDSNBIX=YES;

SPDSSTAG=

Use the SPDSSTAG= macro variable to specify whether to use non-tagged or tagged sorting for PROC SORT or BY processing.

Syntax

SPDSSTAG=YES|NO

Default: NO

Arguments

YES

performs tagged sorting.

performs non-tagged sorting.

Description

During a non-tagged sort, SPD Server attaches the entire table column to the key field(s) to be sorted. Non-tagged sorting allows the software to deliver better performance than a tagged sort. Non-tagged sorting also requires more temporary disk space than a tagged sort.

Example

You are running low on disk space and you do not know whether you have enough disk overhead to accommodate the extra sort space required to support a non-tagged sort operation.

Configure SPD Server to perform a tagged sort.

%let SPDSSTAG=YES;