SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Output 4.5 Smaller Number of Columns in Rows Inserted with a Query

An error occurs if the query selects more columns than what exists for column names in

the table that is specified in the INSERT statement.

Updating Data Values in a Table

You can use the UPDATE statement to modify data values in tables and in the tables that

underlie PROC SQL and SAS/ACCESS views. For more information about updating

views, see “Updating a View” on page 132. The UPDATE statement updates data in

existing columns; it does not create new columns. To add new columns, see “Altering

Columns” on page 123 and “Creating New Columns” on page 27. The examples in this

section update the original NewCountries table.

Updating All Rows in a Column with the Same Expression

The following UPDATE statement increases all populations in the NewCountries table

by 5%:

/* code for all examples in updating section */

libname sql 'SAS-library';

proc sql;

create table sql.newcountries like sql.countries;

insert into sql.newcountries

select * from sql.countries

where population ge 130000000;

proc sql;

update sql.newcountries

set population=population*1.05;

title "Updated Population Values";

select name format=$20.,

capital format=$15.,

population format=comma15.0

from sql.newcountries;

120 Chapter 4 • Creating and Updating Tables and Views

Output 4.6 Updating a Column for All Rows

Updating Rows in a Column with Different Expressions

To update some, but not all, of a column's values, use a WHERE expression in the

UPDATE statement. You can use multiple UPDATE statements, each of which can

contain a different WHERE expression. Each UPDATE statement can have only one

WHERE expression. The following UPDATE statements result in different population

increases for different countries in the NewCountries table.

libname sql 'SAS-library';

proc sql;

create table sql.newcountries like sql.countries;

insert into sql.newcountries

select * from sql.countries

where population ge 130000000;

proc sql;

update sql.newcountries

set population=population*1.05

where name like 'B%';

update sql.newcountries

set population=population*1.07

where name in ('China', 'Russia');

title "Selectively Updated Population Values";

select name format=$20.,

capital format=$15.,

population format=comma15.0

from sql.newcountries;

Updating Data Values in a Table 121

Output 4.7 Selectively Updating a Column

You can accomplish the same result with a CASE expression:

update sql.newcountries

set population=population*

case when name like 'B%' then 1.05

when name in ('China', 'Russia') then 1.07

else 1

end;

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.

CAUTION:

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.

UNDO_POLICY=REQUIRED

is the default. It undoes all updates or inserts up to the point of error.

UNDO_POLICY=NONE

does not undo any updates or inserts.

UNDO_POLICY=OPTIONAL

undoes any updates or inserts that it can undo reliably.

122 Chapter 4 • Creating and Updating Tables and Views