Output 4.7 Selectively Updating a Column
You can accomplish the same result with a CASE expression:
case when name like 'B%' then 1.05
when name in ('China', 'Russia') then 1.07
If the WHEN clause is true, then the corresponding THEN clause returns a value that the
SET clause then uses to complete its expression. In this example, when Name starts with
the letter B, the SET expression becomes population=population*1.05.
Make sure that you specify the ELSE clause. If you omit the ELSE clause, then
each row that is not described in one of the WHEN clauses receives a missing value
for the column that you are updating. This happens because the CASE expression
supplies a missing value to the SET clause, and the Population column is multiplied
by a missing value, which produces a missing value.
Handling Update Errors
While you are updating or inserting rows in a table, you might receive an error message
that the update or insert cannot be performed. By using the UNDO_POLICY= option,
you can control whether the changes that have already been made will be permanent.
The UNDO _POLICY= option in the PROC SQL and RESET statements determines
how PROC SQL handles the rows that have been inserted or updated by the current
INSERT or UPDATE statement up to the point of error.
is the default. It undoes all updates or inserts up to the point of error.
does not undo any updates or inserts.
undoes any updates or inserts that it can undo reliably.
122 Chapter 4 • Creating and Updating Tables and Views