Data Set Options for Relational Databases

ERRLIMIT= Data Set Option

Specifies the number of errors that are allowed before SAS stops processing and issues a rollback.

Default value:	1
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Example
See Also

Syntax

ERRLIMIT=integer

Syntax Description

integer: specifies a positive integer that represents the number of errors after which SAS stops processing and issues a rollback.

Details

SAS ends the step abnormally and calls the DBMS to issue a rollback after a specified number of errors while processing inserts, deletes, updates, and appends. If ERRLIMIT=0, SAS processes all rows no matter how many errors occur. The SAS log displays the total number of rows that SAS processed and the number of failed rows, if applicable.

If the step ends abnormally, any rows that SAS successfully processed after the last commit are rolled back and are therefore lost. Unless DBCOMMIT=1, it is very likely that rows will be lost. The default value is 1000.

Note: A significant performance impact can result if you use this option from a SAS client session in SAS/SHARE or SAS/CONNECT environments to create or populate a newly created table. To prevent this, use the default setting, ERRLIMIT=1. [cautionend]

Teradata: DBCOMMIT= and ERRLIMIT= are disabled for MultiLoad to prevent any conflict with ML_CHECKPOINT=.

Example

In this example, SAS stops processing and issues a rollback to the DBMS at the occurrence of the tenth error. The MYDBLIB libref was assigned in a prior LIBNAME statement.

data mydblib.employee3 (errlimit=10);
   set mydblib.employees;
   where salary > 40000;
run;