SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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;

Output: Creating Employees Table

Output 7.4 The 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;

274 Chapter 7 • SQL Procedure

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: Updating Data in a PROC SQL Table

Output 7.5 Updated Employees Table

Example 3: Updating Data in a PROC SQL Table 275

Example 4: Joining Two Tables

Features: FROM clause

table alias

inner join

joined-table component

PROC SQL statement option

NUMBER

WHERE clause

IN condition

Table names: Proclib.Staff

Proclib.Payroll

Details

This example joins two tables in order to get more information about data that are

common to both tables.

proc sql outobs=10;

title 'Proclib.Staff';

title2 'First 10 Rows Only';

select * from proclib.staff;

title;

Figure 7.3 Proclib.Staff Table

276 Chapter 7 • SQL Procedure

proc sql outobs=10;

title 'Proclib.Payroll';

title2 'First 10 Rows Only';

select * from proclib.payroll;

title;

Figure 7.4 Proclib.Payroll Table

Program

libname proclib 'SAS-library';

proc sql number;

title 'Information for Certain Employees Only';

select Lname, Fname, City, State,

IdNumber, Salary, Jobcode

from proclib.staff, proclib.payroll

where idnumber=idnum and idnum in

('1919', '1400', '1350', '1333');

Program Description

Declare the Proclib library. The Proclib library is used in these examples to store

created tables.

libname proclib 'SAS-library';

Add row numbers to PROC SQL output. NUMBER adds a column that contains the

row number.

Example 4: Joining Two Tables 277

proc sql number;

Specify the title.

title 'Information for Certain Employees Only';

Select the columns to display The SELECT clause selects the columns to show in the

output.

select Lname, Fname, City, State,

IdNumber, Salary, Jobcode

Specify the tables from which to obtain the data. The FROM clause lists the tables to

select from.

from proclib.staff, proclib.payroll

Specify the join criterion and subset the query. The WHERE clause specifies that the

tables are joined on the ID number from each table. WHERE also further subsets the

query with the IN condition, which returns rows for only four employees.

where idnumber=idnum and idnum in

('1919', '1400', '1350', '1333');

Output: Joining Two Tables

Output 7.6 Information for Certain Employees Only

Example 5: Combining Two Tables

Features: DELETE statement

IS condition

RESET statement option

DOUBLE

UNION set operator

Table names: Proclib.Newpay

Proclib.Paylist

Proclib.Paylist2

278 Chapter 7 • SQL Procedure