SQL Procedure

Example 3: Updating Data in a PROC SQL Table

Features:	ALTER TABLE statement: DROP clause MODIFY clause UPDATE statement: SET clause CASE expression
Table name:	Employees
Note:	For information about the input data tables that are used in the examples, see Tables Used in the Examples.

Features:

ALTER TABLE statement:: DROP clause; MODIFY clause

UPDATE statement: SET clause

CASE expression

Table name:

Employees

Note:

For information about the input data tables that are used in the examples, see Tables Used in the Examples.

This example updates data values and drops a column in a table.

Program to Create the Initial Employees_Updated Table

Copy table Employees to table Employees_Updated.In order to preserve the original Employees table, the modifications are made to a copy.

proc sql;
   create table employees_updated as
      select * from employees;

   title 'Initial Employees_Updated Table';
   select * from employees_updated;
quit;

proc sql;
   create table employees_updated as
      select * from employees;

   title 'Initial Employees_Updated Table';
   select * from employees_updated;
quit;

Output: Creating the Initial Employees_Updated Table

The Initial Employees_Updated table

Program to Update the Employees_Updated Table

proc sql;
   update employees_updated
      set salary=salary*
      case when jobcode like '__1' then 1.04
           else 1.025
      end;

   alter table employees_updated
      modify salary num format=dollar8.
      drop phone;
   title 'Updated Employees_Updated Table';
   select * from employees_updated;
quit;

proc sql;
   update employees_updated
      set salary=salary*
      case when jobcode like '__1' then 1.04
           else 1.025
      end;

   alter table employees_updated
      modify salary num format=dollar8.
      drop phone;
   title 'Updated Employees_Updated Table';
   select * from employees_updated;
quit;

Program Description

Update the values in the Salary column. The UPDATE statement updates the values in Employees. The SET clause specifies that the data in the Salary column be multiplied by 1.04 when the job code ends with a 1 and 1.025 for all other job codes. (The two underscores represent any character.) The CASE expression returns a value for each row that completes the SET clause.

proc sql;
   update employees_updated
      set salary=salary*
      case when jobcode like '__1' then 1.04
           else 1.025
      end;

Modify the format of the Salary column and delete the Phone column. The ALTER TABLE statement specifies Employees as the table to alter. The MODIFY clause permanently modifies the format of the Salary column. The DROP clause permanently drops the Phone column.

   alter table employees_updated
      modify salary num format=dollar8.
      drop phone;

Specify the title.

   title 'Updated Employees_Updated Table';

Display the entire updated Employees_Updated table. The SELECT clause displays the Employees_Updated table after the updates. The asterisk (*) selects all columns.

   select * from employees_updated;
quit;

Output: Updating Data in a PROC SQL Table

The Modified Employees_Updated Table

Last updated: September 16, 2026