Accessing DBMS Data with the LIBNAME Statement Joining DBMS and SAS Data 261
Output 13.21 Using the WHERE Statement
High Bills--Not Paid 1
Obs billedon invoicenum amountinus billedto
1 05OCT1998 11271 $11,063,836.00 18543489
2 10OCT1998 11286 $11,063,836.00 43459747
3 02NOV1998 12051 $2,256,870.00 39045213
4 17NOV1998 12102 $11,063,836.00 18543489
5 27DEC1998 12471 $2,256,870.00 39045213
6 24DEC1998 12476 $2,256,870.00 38763919
Joining DBMS and SAS Data
This example shows how to combine SAS and DBMS data using the SAS/ACCESS
LIBNAME statement. The example creates a PROC SQL view, Work.Emp_Csr, from
the DB2 table Employees and joins the view with a SAS data set, TempEmps, to select
only interns who are family members of existing employees.
libname mydblib db2 ssid=db2;
title ’Interns Who Are Family Members of Employees’;
proc sql;
create view emp_csr as
select * from mydblib.employees
where dept in (’CSR010’, ’CSR011’, ’CSR004’);
select tempemps.lastname, tempemps.firstnam,
tempemps.empid, tempemps.familyid,
tempemps.gender, tempemps.dept,
tempemps.hiredate
from emp_csr, samples.tempemps
where emp_csr.empid=tempemps.familyid;
quit;
Output 13.22 Combining a PROC SQL View with a SAS Data Set
Interns Who Are Family Members of Employees 1
lastname firstnam empid familyid gender dept hiredate
-----------------------------------------------------------------------------
SMITH ROBERT 765112 234967 M CSR010 04MAY1998
NISHIMATSU-LYNCH RICHARD 765111 677890 M CSR011 04MAY1998
