SAS Scalable Performance Data (SPD) Server Macro Variables

• Introduction
• Variable for Compatibility with the Base SAS Engine
  • SPDSBNEQ=
• Variables for Miscellaneous Functions
  • SPDSEOBS=
  • SPDSSOBS=
  • SPDSUSAV=
  • SPDSUSDS=
  • SPDSVERB=
  • SPDSFSAV=
  • SPDSEINT=
• Variables for Sorts
  • SPDSBSRT=
  • SPDSNBIX=
  • SPDSSTAG=
• Variables for WHERE Clause Evaluations
  • SPDSTCNT=
  • SPDSEV1T=
  • SPDSEV2T=
  • SPDSWDEB=
  • SPDSIRAT=
  • SPDSNIDX=
  • SPDSWCST=
  • SPDSWSEQ=
• Variables That Affect Disk Space
  • SPDSCMPF=
  • SPDSDCMP=
  • SPDSIASY=
  • SPDSSIZE=
• Variables To Enhance Performance
  • SPDSNETP=
  • SPDSSADD=
  • SPDSSYRD=
  • SPDSAUNQ=
• Variables for a Client and a Server Running on the Same UNIX Machine
  • SPDSCOMP=

Introduction

Macro variables, known as symbolic variables, operate similarly to LIBNAME and table options. But, they have an advantage because they apply globally. That is, their value remains constant until explicitly changed.

This chapter presents reference information for SPD Server macro variables, including their purpose, default values, and when and how to use them. The variables are grouped by function or purpose of the default value. Changing the value can also change the purpose, making the variable fall into another group. 

For example, the default setting for the macro variable SPDSSADD= is NO. The SPDSSADD= macro enhances performance during data appends. Setting SPDSSADD=  to YES changes the way the variable functions.  The macro setting SPDSADD=YES ensures compatibility with the base SAS engine.  The default setting improves performance.  Changing the setting from the default improves Base SAS software compatibility. 

To set a macro variable to YES submit the following statement:

%let MACROVAR=YES;

Note:  Assignments for macro variables with YES|NO arguments must be entered in uppercase (capitalized).

When you specify table option settings, precedence matters.  If you specify a table option after you set the option in a macro variable statement, the table option setting takes precedence over the macro variable option setting.  If you specify an option using  a LIBNAME statement, then later specify an option setting through a macro variable statement, the table option setting made in the macro variable takes precedence over the over the LIBNAME statement setting.

To view the default values for the SPD Server macro variables, use the SPDSMAC command associated with PROC SPDO.  SAS displays the macro variables and their current settings. Understanding proper use of macro variables in SPD Server allows you to unleash the power of the software.

Variable for Compatibility with the Base SAS Engine

SPDSBNEQ=

Use the SPDSBNEQ= setting to specify the output order of table rows that have identical values in the BY column.

Syntax
SPDSBNEQ=YES|NO

Default: NO

Corresponding Table Option: BYNOEQUALS=

Use the following arguments:

YES

outputs rows with identical values in a BY clause in random order.

NO

outputs rows with identical values in a BY clause using the relative table position of the rows from the input table.

Description
SPDSBNEQ=NO configures the SPD Server to imitate the Base SAS engine behavior. If strict compatibility is not required, assign SPDSBNEQ=YES. Random output allows the SPD Server to create indexes and append to tables faster.

Example
Configure the SPD Server so that it output table rows as quickly as possible when processing rows that have identical values in the BY column.

%let SPDSBNEQ=YES;

Variables for Miscellaneous Functions

SPDSEOBS=

Use the SPDSEOBS= macro variable to specify the number of the last row (end observation) of a user-defined range that you want to process in a table.

Syntax
SPDSEOBS=n

Default:  The default setting of 0 processes the entire table.

Corresponding Table Option: ENDOBS=

Use the following argument:

  n
         is the number of the end row.

Description
The SPD Server processes the entire table by default unless you specify a range of rows. You can specify a range using the macro variables SPDSSOBS= and SPDSEOBS=, or you can use the table options, STARTOBS= and ENDOBS=.

If you use the range start macro variable SPDSSOBS= without specifying an end range value using the SPDSEOBS= macro variable, SPD Server processes to the last row in the table. If you specify values for both SPDSSOBS= and SPDSEOBS= macro variables, the value of SPDSEOBS= must be greater than SPDSSOBS=.  The SPDSSOBS= and SPDSEOBS= macro variables specify ranges for table input processing as well as WHERE Clause processing.

Example
In order to create test tables, you configure the SPD Server to subset the first 100 rows of each table in your job.  Submit the macro variable statement for SPDSEOBS= at the beginning of your job.

%let SPDSEOBS=100;

SPDSSOBS=

Use the SPDSSOBS= macro variable to specify the number of the starting row (observation) in a user-defined range of a table.

Syntax
SPDSSOBS=n

Default:  The default setting of 0 processes the entire table.

Corresponding Table Option: STARTOBS=

Use the following argument:

n
      is the number of the start row.

Description
By default, SPD Server processes entire tables unless you specify a range of rows. You can specify a range using the macro variables SPDSSOBS= and SPDSEOBS=, or you can use the table options, STARTOBS= and ENDOBS=.

If you specify the end of a user-defined range using the SPDSEOBS= macro variable, but do not implicitly specify the beginning of the range using SPDSSOBS=, SPD Server sets SPDSSOBS= to 1, or the first row in the table. If you specify values for both SPDSSOBS= and SPDSEOBS= macro variables, the value of SPDSEOBS= must be greater than SPDSSOBS=.  The SPDSSOBS= and SPDSEOBS= macro variables specify ranges for table input processing as well as WHERE Clause processing.

Example
Print the INVENTORY.OLDAUTOS table, skipping rows 1-999 and beginning with row 1000.  You should submit the SPDSSOBS= macro variable statement before the PROC PRINT statement in your job.

%let SPDSSOBS=1000;

The statement above specifies the starting row with SPDSSOBS=, but does not declare an ending row for the range using SPDSEOBS=.  When the program executes, SAS will begin printing at row 1000 and continues until the final row of the table is reached.

PROC PRINT data=inventory.oldautos;
run;

SPDSUSAV=

Use the SPDSUSAV= macro variable to specify whether to save rows with non-unique (rejected) keys to a separate SAS table.

Syntax
SPDSUSAV=YES|NO|REP

Default: NO

Affected by Table Option : SYNCADD=

Use in Conjunction with Variable : SPDSUSDS=

Corresponding Table Option : UNIQUESAVE=

Use the following arguments:

YES

writes rows with non-unique key values to a SAS table.  Use the macro variable SPDSUSDS= to reference the name of the SAS table for the rejected keys.

 

NO

non-unique key values are ignored and rejected rows are not written to a separate table.

 

REP

when updating a master table from a transaction table, where the two tables share identical variable structures, the SPDSUSAV=REP option replaces the row updated row in the master table instead of appending a row to the master table. The REP option only functions in the presence of a /UNIQUE index on the MASTER table. Otherwise, the REP setting is ignored..

 

 

Description
When performing an append operation, SPD Server does not save the rows which contain duplicate key values unless the SPDSUSAV= macro variable is set to YES. 

When SPDSUSAV= is set to YES, SPD Server creates a hidden SAS table and writes rejected rows to the table. Use the SPDSUSDS= macro variable command to view the contents of the table.  Each append operation creates a different table.

Example
Append several tables to the EMPLOYEE table, using employee number as a unique key. The appended tables should not have records with duplicate employee numbers. 

At the beginning of the job, configure SPD Server to write any rejected (identical) employee number records to a SAS table.  The macro variable SPDSUSDS= holds the name of the SAS table for the rejected keys.

%let SPDSUSAV=YES

Use a %PUT statement to display the name of the table, and then print the table.

%put Set the macro variable spdsusds to &spdsusds;

title 'Duplicate (non-unique) employee numbers found in
       EMPS';
PROC PRINT data=&spdsusds; run;

SPDSUSDS=

Use the SPDSUSDS= macro variable to reference the name of the SAS table that SPD Server creates for duplicate or rejected keys when the SPDSUSAV= macro variable is set to YES.

Syntax
SPDSUSDS=

Default: SPD Server automatically generates identifying strings for the duplicate or rejected key tables.

Use in Conjunction with Table Option: SYNCADD=

Use in Conjunction with Variable: SPDSUSAV=

Corresponding Table Option: UNIQUESAVE=

Description
When SPDSUSAV= or UNIQUESAVE= is set to YES, SPD Server creates a table to store any rows with duplicate key values encountered during an append operation. Submitting the SPDSUSDS= macro variable references the generated name for the hidden SAS table.

To obtain the name and print the table's contents, reference the variable SPDSUSDS=.

Example

%let SPDSUSAV=YES

Use a %PUT statement to display the name of the table created by SPDSUSDS= and to print out the duplicate rows.

%put Set the macro variable spdsusds to &spdsusds;

title 'Duplicate Rows Found in MYTABLE
       During the Last Data Append';
PROC PRINT data=&spdsusds; run;

SPDSVERB=

Use the SPDSVERB= macro variable to provide verbose details on all  indexes, ACL information, and other information that is associated with SPD Server tables.  

Syntax
SPDSVERB=YES|NO

Default: NO

Corresponding Table Option: VERBOSE=

Use the following arguments:

YES

requests detail information for indexes, ACLs, and other SPD Server table values.

 

NO

suppresses detail information for indexes, ACLs, and other SPD Server table values.

Example
You need information about associated indexes for the SPD Server table SUPPLY.  Configure SPD Server for verbose details at the start of your session so you can see index details. Submit the SPDSVERB= macro variable as a line in your your autoexec.sas file:

%let SPDSVERB=YES;

Submit a PROC CONTENTS request for the SUPPLY table:

PROC CONTENTS data=supply;
run;

SPDSFSAV=

Use the SPDSFSAV= macro variable to specify whether you want to retain table data if the SPD Server table creation process terminates abnormally.

Syntax
SPDSFSAV=YES|NO

Default: NO.  Normally SAS closes and deletes tables which are not properly created.

Use the following arguments:

YES

enables FORCESAVE mode and saves the table.

 

NO

default SPD Server actions delete partially completed tables.

Description
Large tables can require a long time to create. If problems such as network interruptions or disk space shortages occur during this time period, the table may not be properly created and signal an error condition. If SAS encounters such an error condition, it deletes the partially completed table.

In SPD Server you can set SPDSFSAV=YES.  Saving the partially created table can protect the time and resources invested a in long-running job. When the SPDSFSAV= macro variable is set to YES, the SPD Server LIBNAME proxy saves partially completed tables in their last state and identifies them as damaged tables.

Marking the table damaged prohibits other SAS DATA or PROC steps from accessing the table until its state of completion can be verified. After you verify or repair a table, you can clear the 'damaged' status and enable further read/update/append operations on the table. Use the PROC DATASETS REPAIR operation to remove the damaged file indicator.

Example
Configure SPD Server before you run the table creation job for a large table called ANNUAL.  If some error prevents the successful completion of the table ANNUAL, the partially completed table will be saved.

%let SPDSFSAV=YES;
DATA SPDSLIB.ANNUAL;
...
RUN;

SPDSEINT=

Use the SPDSEINT= macro to specify how SPD Server responds to network disconnects during SQL pass-through EXECUTE() statements.

Syntax
SPDSEINT=YES|NO

Default: YES

Description:
The SPD Server SQL server interrupts SQL processing by default when a network failure occurs . The interruption prematurely terminates the EXECUTE() statement. Setting SPDSEINT=NO configures the SPD Server's SQL server to continue processing until completion regardless of network disconnects.

Warning: Use the macro variable setting SPDSEINT=NO carefully! A runaway EXECUTE() statement requires a privileged system user on the server machine to kill the SPD Server SQL proxy process. This is the only way to stop the processing.

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=

Use the following arguments:

YES

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

 

NO

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 Connecting to SAS Scalable Performance Data Server. 

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

Use the following 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.
 

 

NO

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

Use the following arguments:

YES

performs tagged sorting.

 

NO

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 don't know if 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;