Example: Combine an SQL View with a SAS Data Set

This example joins SAS data with Oracle data that is retrieved by using a pass-through query in a PROC SQL SELECT statement.

Information about student interns is stored in the SAS data file, Samples.TempEmps. The Oracle data is joined with this SAS data file to determine whether any of the student interns have a family member who works in the CSR departments.

To join the data from Samples.TempEmps with the data from the pass-through query, you assign a table alias (Query1) to the query. Doing so enables you to qualify the query's column names in the WHERE clause.

options ls=120;

title 'Interns Who Are Family Members of Employees';

proc sql;
connect to oracle as mydb;
%put &sqlxmsg;

select tempemps.lastname, tempemps.firstnam, tempemps.empid,
       tempemps.familyid, tempemps.gender, tempemps.dept,
       tempemps.hiredate
   from connection to mydb
      (select * from employees) as query1, samples.tempemps
   where query1.empid=tempemps.familyid;
%put &sqlxmsg;

disconnect from mydb;
quit;

                   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

When SAS data is joined to DBMS data through a pass-through query, PROC SQL cannot optimize the query. In this case it is much more efficient to use a SAS/ACCESS LIBNAME statement. Yet there is another way to increase efficiency: extract the DBMS data, place the data in a new SAS data file, assign SAS indexes to the appropriate variables, and join the two SAS data files.