30 May

SAS: Constraint Messages

SAS allows you to create custom messages when a constraint occurs.  There are two options for a constraint:

  • MESSAGE=:  This is a custom message up to 250 characters.
  • MSGTYPE=:  This has two options.  NEWLINE means that it is displayed in addition to the original error message.  USER means that only the custom message is displayed.

Here’s a couple of examples that should make it clear.

proc sql UNDO_POLICY=REQUIRED;
CREATE TABLE work.myjunk
(
	number int,
	number1 int CHECK (number < 5) Message='***This is my error***' Msgtype=USER
);
INSERT INTO work.myjunk
   VALUES (6,6);
SELECT * FROM work.myjunk;
quit;

ERROR: ***This is my error*** 
NOTE: This insert failed while attempting to add data from VALUES clause 1 to the data set.

proc sql UNDO_POLICY=REQUIRED;
CREATE TABLE work.myjunk
(
	number int,
	number1 int CHECK (number < 5) Message='***This is my error***' Msgtype=NEWLINE
);
INSERT INTO work.myjunk
   VALUES (6,6);
SELECT * FROM work.myjunk;
quit;

ERROR: ***This is my error*** Add/Update failed for data set WORK.MYJUNK because data value(s) do not comply with integrity
constraint _CK0001_.

NEWLINE is the default option.

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.