UNDO_POLICY=NONE|OPTIONAL|REQUIRED specifies how PROC SQL handles updated data if errors occur while you are updating data. You can use UNDO_POLICY= to control whether your changes will be permanent: NONE keeps any updates or inserts. OPTIONAL reverses any updates or inserts that it can reverse reliably. REQUIRED reverses all inserts or updates that have been done to the point of the error. In some cases, the UNDO operation cannot be done reliably. For example, when a program uses a SAS/ACCESS view, it might not be able to reverse the effects of the INSERT and UPDATE statements without reversing the effects of other changes at the same time. In that case, PROC SQL issues an error message and does not execute the statement. Also, when a SAS data set is accessed through a SAS/SHARE server and is opened with the data set option CNTLLEV=RECORD, you cannot reliably reverse your changes. This option can enable other users to update newly inserted rows. If an error occurs during the insert, then PROC SQL can delete a record that another user updated. In that case, the statement is not executed, and an error message is issued. Default: REQUIRED Tip: If you are updating a data set using the SPD Engine, you can significantly improve processing performance by setting UNDO_POLICY=NONE. However, ensure that NONE is an appropriate setting for your application. Tip: Alternatively, you can set the SQLUNDOPOLICY system option. The value that is specified in the SQLUNDOPOLICY= system option is in effect for all SQL procedure statements, unless the PROC SQL UNDO_POLICY= option 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. After the procedure completes, the undo policy reverts to the value of the SQLUNDOPOLICY= system option. For more information, see the SQLUNDOPOLICY system option in the SAS Language Reference: Dictionary. http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473669.htm Related System Option Information: 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. http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003157487.htm
... View more