29 May

SAS: UNDO_POLICY

This option tells the proc sql statement what to do if an INSERT or UPDATE fails for some reason. Usually this would be a constraint violation but it could also occur because of data type mismatch or probably something else that I’m not thinking about right this second.

This option has 3 settings: REQUIRED, OPTIONAL and NONE

REQUIRED

Proc sql will undo an INSERT / UPDATE if it fails.

This is the default option.

NONE

Skips INSERT / UPDATE records that fail.  In other words, if you have 3 statements, and the second fails, statements one and three will still run.

When this happens a warning is written to the log.

OPTIONAL

In this case, if SAS can undo the statement, it will.  If, for some reason, it cannot then it will not.

There are two cases where UNDO is not reliable, which I don’t really understand, at this time.  For now, it appears they have something to do with SAS/SHARE and SAS/ACCESS.

SAS likens UNDO_POLICY to a rollback in a transaction, which is probably an OK comparison but this isn’t a rollback and SAS doesn’t support the ROLLBACK statement.

Here’s an example:

proc sql UNDO_POLICY=OPTIONAL;
CREATE TABLE work.myjunk
(
	number int,
	number1 int CHECK (number < 5)
);
INSERT INTO work.myjunk
   VALUES (1,1)
   VALUES (2,2)
   VALUES (6,6);
SELECT * FROM work.myjunk;
quit;

ERROR: Add/Update failed for data set WORK.MYJUNK because data value(s) do not comply with 
integrity constraint _CK0001_.
NOTE: This insert failed while attempting to add data from VALUES clause 3 to the data set.
NOTE: Deleting the successful inserts before error noted above to restore table to a consistent 
state.

proc sql UNDO_POLICY=NONE;
CREATE TABLE work.myjunk
(
	number int,
	number1 int CHECK (number < 5)
);
INSERT INTO work.myjunk
   VALUES (1,1)
   VALUES (2,2)
   VALUES (6,6);
SELECT * FROM work.myjunk;
quit;

ERROR: Add/Update failed for data set WORK.MYJUNK because data value(s) do not comply with 
integrity constraint _CK0001_.
NOTE: This insert failed while attempting to add data from VALUES clause 3 to the data set.
NOTE: 3 rows were inserted into WORK.MYJUNK -- of these 1 row was rejected as an ERROR, leaving 
2 rows that were inserted successfully.

proc sql UNDO_POLICY=REQUIRED;
CREATE TABLE work.myjunk
(
	number int,
	number1 int CHECK (number < 5)
);
INSERT INTO work.myjunk
   VALUES (1,1)
   VALUES (2,2)
   VALUES (6,6);
SELECT * FROM work.myjunk;
quit;

ERROR: Add/Update failed for data set WORK.MYJUNK because data value(s) do not comply with 
integrity constraint _CK0001_.
NOTE: This insert failed while attempting to add data from VALUES clause 3 to the data set.
NOTE: Deleting the successful inserts before error noted above to restore table to a consistent 
state.

As you can see they all fail, but, the one where the UNDO_POLICY was set to NONE did not rollback the two inserts that were successful.

Leave a Reply

Your email address will not be published. Required fields are marked *