Variables for Miscellaneous Functions

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.

SPDSEOBS=n

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

Corresponding Table Option: ENDOBS=

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.

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;

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

SPDSSOBS=n

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

Corresponding Table Option: STARTOBS=

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.

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;

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

SPDSUSAV=YES|NO|REP

Default: NO

Affected by Table Option : SYNCADD=

Use in Conjunction with Variable : SPDSUSDS=

Corresponding Table Option : UNIQUESAVE=

Arguments

When performing an append operation, SPD Server does not save the rows that 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.

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 (nonunique) employee numbers found in
       EMPS';
PROC PRINT data=&spdsusds run;

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.

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=

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=.

%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;  

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

SPDSVERB=YES|NO

Default:NO

Corresponding Table Option:VERBOSE=

Arguments

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 autoexec.sas file:

%let SPDSVERB=YES;

Submit a PROC CONTENTS request for the SUPPLY table:

PROC CONTENTS data=supply;
run;

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

SPDSFSAV=YES|NO

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

Arguments

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 might 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 in a 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.

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;  

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

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.