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 to Create the Employee Table

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

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

Output: Listing

                                Employees Table                                1

          Id                              Job
          Num   LName        FName        Code    Salary  Phone
          ------------------------------------------------------------
          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 to Update the Employee Table

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: Listing

                                Employees Table                                1

          Id                              Job
          Num   LName        FName        Code    Salary  Phone
          ------------------------------------------------------------
          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

                            Updated Employees Table                            2

                 Id                              Job
                 Num   LName        FName        Code    Salary
                 ----------------------------------------------
                 1876  CHIN         JACK         TA1    $44,096
                 1114  GREENWALD    JANICE       ME3    $38,950
                 1556  PENNINGTON   MICHAEL      ME1    $31,054
                 1354  PARKER       MARY         FA3    $67,445
                 1130  WOOD         DEBORAH      PT2    $37,427

Top of Page