Example 3: Updating Data in a PROC SQL Table

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;

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;

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;