Creating and Updating Tables and Views

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 five percent:

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

                           Updated Population Values

             Name                  Capital               Population
             ------------------------------------------------------
             Brazil                Brasilia             168,325,875
             China                 Beijing            1,262,325,831
             India                 New Delhi            975,459,576
             Indonesia             Jakarta              212,513,552
             Russia                Moscow               158,644,478
             United States         Washington           276,459,548

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.

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

                     Selectively Updated Population Values

             Name                  Capital               Population
             ------------------------------------------------------
             Brazil                Brasilia             168,325,875
             China                 Beijing            1,286,370,132
             India                 New Delhi            929,009,120
             Indonesia             Jakarta              202,393,859
             Russia                Moscow               161,666,278
             United States         Washington           263,294,808

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 the SQLUNDOPOLICY system option in SAS Language Reference: Dictionary. [cautionend]

Top of Page