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

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

Program to Create the Employee Table

proc sql;
   title 'Employees Table';
   select * from Employees;

Program Description

Display the entire EMPLOYEES table. The SELECT clause displays the table before the updates. The asterisk (*) selects all columns for display. The FROM clause specifies EMPLOYEES as the table to select from.

proc sql;
   title 'Employees Table';
   select * from Employees;

Employees Table

Program to Update the Employee Table

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

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

title 'Updated Employees Table';

 select * from employees;

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
      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
      modify salary num format=dollar8.
      drop phone;

Specify the title.

title 'Updated Employees Table';

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

 select * from employees;

Output

Updated Employees Table