O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required