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. The UPDATE statement updates data in existing columns; it does not create new columns. To add new columns, see Altering Columns and Creating New Columns. 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;
   delete from sql.newcountries;
   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;

Updating a Column for All Rows

Updating Rows in a Column with Different Expressions

If you want to update some, but not all, of a column's values, then use a WHERE expression in the UPDATE statement. You can use multiple UPDATE statements, each with a different expression. However, each UPDATE statement can have only one WHERE clause. The following UPDATE statements result in different population increases for different countries in the NEWCOUNRTRIES table.

libname sql 'SAS-library';

proc sql;
   delete from sql.newcountries;
   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;

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.

Note: Alternatively, you can set the SQLUNDOPOLICY system option. For more information, see SQLUNDOPOLICY= System Option.