To modify data values in some or all of the existing rows in a table, you use the UPDATE statement in PROC SQL. In the UPDATE statement, for each column whose rows you want to modify, you specify an expression that indicates how the values should be modified. For example, the following expression indicates that the values for the column Units should be multiplied by 4:

units=units*4

You can use the UPDATE statement in two main ways.

proc sql;
   update work.payrollmaster_new
      set salary=salary*1.05
      where jobcode like '__1';

proc sql;
   update  work.payrollmaster_new 
      set salary=salary*
         case when substr(jobcode,3,1)='1'
                 then 1.05
              when substr(jobcode,3,1)='2'
                 then 1.10
              when substr(jobcode,3,1)='3'
                 then 1.15
              else 1.08
         end;

We consider each of these methods for updating existing rows in a table.

To update all (or a subset of) rows in a column with the same expression, use an UPDATE statement that contains a SET clause and a possible WHERE clause.

Suppose a company is considering giving all level-1 employees a 5% raise. Employee salaries are stored in the table Sasuser.Payrollmaster. You do not want to update the original table, so you create a temporary copy of Sasuser.Payrollmaster, called Work.Payrollmaster_New. The following PROC SQL step creates Work.Payrollmaster_New based on a query result and generates an output report of the new table:

proc sql;
   create table work.payrollmaster_new as
      select *
         from sasuser.payrollmaster;
   select *
      from work.payrollmaster_new;

The first 10 rows of Work.Payrollmaster_New, the table in which you update salaries, are shown below.

Next, you write a PROC SQL step that updates the specified rows. The UPDATE statement contains both of the following:

Finally, you can use a SELECT statement to display the updated table as a report. The first 10 rows of Work.Payrollmaster_New, with updates, are shown below.

The third row lists data for a level-1 employee, and that person's salary has been updated.

If you wanted to increase all of the salaries, you would simply remove the WHERE clause from the UPDATE statement:

proc sql;
   update work.payrollmaster_new
      set salary=salary*1.05;

Sometimes you want to use different expressions to modify values for different subsets of rows within a column.

For example, instead of only raising the salary of level-1 employees by 5%, you might also want to raise the salaries of level-2 employees by 10%, and so on, using a different percentage increase for each group of employees.

There are two possible ways to use different expressions to update different subsets of rows.

proc sql;
   update work.payrollmaster_new
      set salary=salary*1.05
         where substr(jobcode,3,1)='1';
   update work.payrollmaster_new
      set salary=salary*1.10
         where substr(jobcode,3,1)='2';
   update work.payrollmaster_new
      set salary=salary*1.15
         where substr(jobcode,3,1)='3';

proc sql;
   update work.payrollmaster_new 
      set salary=salary*
         case 
              when substr(jobcode,3,1)='1'
                 then 1.05
              when substr(jobcode,3,1)='2'
                 then 1.10
              when substr(jobcode,3,1)='3'
                 then 1.15
              else 1.08
         end;

The first method, which requires the use of multiple UPDATE statements, is cumbersome because the SET statement and expression must be repeated in each UPDATE statement. In this example, the first method is inefficient because the table Work.Payrollmaster_New must be read three times.

The second method, which uses conditional processing (the CASE expression), is recommended. We now consider the second method.

To update different subsets of rows in a table in different ways, you can incorporate conditional processing by using the CASE expression in the SET clause of an UPDATE statement. The CASE expression selects result values that satisfy specified conditions.

In the following UPDATE statement, the CASE expression contains three WHEN-THEN clauses that specify three different subsets of rows in the table Work.Insure_New:

update work.insure_new
   set pctinsured=pctinsured*
      case 
         when company='ACME' 
              then 1.10
         when company='RELIABLE' 
              then 1.15
         when company='HOMELIFE' 
              then 1.25 
         else 1 
      end;

PROC SQL updates each specified subset of rows differently, according to the corresponding WHEN-THEN (or ELSE) clause.

When you specify a CASE expression, PROC SQL updates each row as follows:

The use of the CASE expression is efficient because of how PROC SQL processes the WHEN-THEN clauses. The WHEN-THEN clauses in the CASE expression are evaluated sequentially. When a matching case is found, the THEN expression is evaluated and set, and the remaining WHEN cases are not considered.

In the next few sections, you learn about the use of the CASE expression in the UPDATE statement, without and with the optional case operand:

Here is an example of an UPDATE statement that uses the CASE expression for conditional processing. This example shows the form of the CASE expression that does not include the optional case operand.

Suppose a company is considering giving raises to all of its employees, with a different percentage for each employee level:

First, you create the temporary table Work.Payrollmaster3, which is a copy of Sasuser.Payrollmaster, the table containing the employee salary data. The first 10 rows of Work.Payrollmaster3 are shown below.

Next, you create a PROC SQL step that updates rows by using an UPDATE statement that contains a SET clause and a CASE expression:

proc sql;
   update work.payrollmaster3
      set salary=salary*
         case 
            when substr(jobcode,3,1)='1'
                 then 1.05
            when substr(jobcode,3,1)='2'
                 then 1.10
            when substr(jobcode,3,1)='3'
                 then 1.15
            else 1.08
         end;

In this example, the CASE expression contains three WHEN clauses, one for each subset of rows (level-1, level-2, and level-3 employees), followed by an ELSE clause to handle any rows that do not meet the expected conditions.

The first 10 rows of Work.Payrollmaster3, after the rows have been updated, are shown below.

By comparing the values of Salary in the original and updated versions of Work.Payrollmaster3 (as shown above), you can see how the values changed according to the job level indicated in the JobCode.

If the expression in the SET clause uses an equals (=) comparison operator, you might use the optional case operand in the CASE expression. Consider PROC SQL step that was shown in the preceding example, and see how the CASE expression in the UPDATE statement can be rewritten by using the alternate syntax.

In the following PROC SQL step, which was shown earlier, the CASE expression contains three WHEN-THEN clauses. These clauses contain similar expressions, each of which specifies the same SUBSTR function:

proc sql;
   update work.payrollmaster_new2 
      set salary=salary*
         case 
            when substr(jobcode,3,1)='1'
                   then 1.05
            when substr(jobcode,3,1)='2'
                   then 1.10
            when substr(jobcode,3,1)='3'
                   then 1.15
            else 1.08
         end;

Because the expression in this SET clause uses an equals (=) operator, you can restructure the CASE expression for more efficient processing. In the alternate syntax, the repeated SUBSTR function is removed from each WHEN-THEN clause and is placed after the keyword CASE, as an operand:

proc sql;
   update work.payrollmaster_new2 
      set salary=salary*
         case substr(jobcode,3,1)
            when '1'
                 then 1.05
            when '2'
                 then 1.10
            when '3'
                 then 1.15
            else 1.08
         end;

Using the alternate syntax, the SUBSTR function is evaluated only once, so this PROC SQL step is more efficient than the original version.

You can use the CASE expression in three different PROC SQL statements: UPDATE, INSERT, and SELECT. In the SELECT statement, you can use the CASE expression within a new column definition to specify different values for different subsets of rows.

Suppose you want to generate an output report that displays employee names, job codes, and job levels. Your PROC SQL query selects LastName and FirstName from Sasuser.Staffmaster, and JobCode from Sasuser.Payrollmaster. The SELECT statement must define JobLevel as a new column, because it does not exist as a separate column in either table.

You want to assign the values of JobLevel, based on the number at the end of each jobcode. (The number at the end of each JobCode value is expected to be 1, 2, or 3.) To create JobLevel, you can use the case operand form of the CASE expression to specify the three possible conditions (plus an ELSE condition, just in case).

The PROC SQL query is shown below:

proc sql outobs=10;
   select lastname, firstname, jobcode, 
          case substr(jobcode,3,1)
             when '1'
                  then 'junior'
             when '2'
                  then 'intermediate'
             when '3'
                  then 'senior'
             else 'none'
          end as JobLevel
      from sasuser.payrollmaster,
           sasuser.staffmaster
      where staffmaster.empid=
            payrollmaster.empid;

The SELECT clause uses the CASE expression to assign a value of junior, intermediate, senior, or none to each row in the new JobLevel column.