The SQL Procedure

Example 3: Updating Data in a PROC SQL Table

Procedure features:

ALTER TABLE statement

	DROP clause
	MODIFY clause

UPDATE statement

SET clause

CASE expression

Table:

EMPLOYEES

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

Input

data Employees;
   input IdNum $4. +2 LName $11. FName $11. JobCode $3.
          +1 Salary 5. +1 Phone $12.;
   datalines;
1876  CHIN       JACK       TA1 42400 212/588-5634
1114  GREENWALD  JANICE     ME3 38000 212/588-1092
1556  PENNINGTON MICHAEL    ME1 29860 718/383-5681
1354  PARKER     MARY       FA3 65800 914/455-2337
1130  WOOD       DEBORAH    PT2 36514 212/587-0013
;

Program

options nodate pageno=1 linesize=80 pagesize=40;

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

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;

Output

Chapter Contents
Previous
Next
Top of Page