Accessing DBMS Data with the LIBNAME Statement

Joining DBMS and SAS Data

This example shows how to combine SAS and DBMS data using the SAS/ACCESS LIBNAME statement. The example creates an 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;

Combining an 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

Top of Page