O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required