Example: Join DBMS and SAS Data

This example shows how to combine SAS and DBMS data using the SAS/ACCESS LIBNAME statement. It creates an SQL view, Work.Emp_Csr, from the Employees DB2 table 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;

                   Interns Who Are Family Members of Employees

    lastname          firstnam   empid    familyid    gender   dept     hiredate
   
-----------------------------------------------------------------------------
    SMITH             ROBERT     765112   234967      M        CSR010   04MAY1998
    NISHIMATSU-LYNCH  RICHARD    765111   677890      M        CSR011   04MAY1998