SAS System Options

SQLUNDOPOLICY= System Option

Specifies whether the SQL procedure keeps or discards updated data if errors occur while the data is being updated.

Valid in:	configuration file, SAS invocation, Options statement
Category:	Files: SAS Files
	System administration: SQL
PROC OPTIONS GROUP=	SASFILES
	SQL

Syntax
Syntax Description
Details
See Also

Syntax

SQLUNDOPOLICY=NONE | OPTIONAL | REQUIRED

Syntax Description

NONE

specifies to keep changes that are made by the INSERT and UPDATE statements.

OPTIONAL

specifies to reverse changes that are made by the INSERT and UPDATE statements as long as reversing the changes is reliable.

REQUIRED

specifies to undo all changes that are made by the INSERT and UPDATE statements, up to the point of the error. This is the default.

CAUTION:: Some UNDO operations cannot reliably reverse changes.
In some situations, reversing the effects of the INSERT and UPDATE statements cannot be done reliably. When operations cannot be reversed, the SQL procedure issues an error message and does not execute the statement. For example, when a program uses a SAS/ACCESS view, or when a SAS data set is accessed through a SAS/SHARE server and is opened with the data set option CNTLLEV=RECORD, changes cannot be reliably reversed.

CAUTION:: Some UNDO operations might not reverse changes.
In situations where multiple transactions are made to the same record, PROC SQL might not reverse a change; it will issue an error message instead. For example, if an error occurs during an insert, PROC SQL can delete a record that another user updated. In that case, the UNDO statement is not executed, and an error message is issued.

Details

The value that is specified in the SQLUNDOPOLICY= system option is in effect for all SQL procedure statements, unless the UNDO_POLICY option in the PROC SQL statement is set. The value of the UNDO_POLICY option takes precedence over the SQLUNDOPOLICY= system option. The RESET statement can also be used to set or reset the UNDO_POLICY option. However, changing the value of the UNDO_POLICY option does not change the value of the SQLUNDOPOLICY= system option. Once the procedure completes, the undo policy reverts to the value of the SQLUNDOPOLICY= system option.

If you are updating a data set using the SPD Engine, you can significantly improve processing performance by setting SQLUNDOPOLICY=NONE. However, ensure that NONE is an appropriate setting for your application.