SAS/ACCESS Software for Relational Databases: Reference

Joining DBMS and SAS Data

This example shows two ways to combine SAS and DBMS data. The first method uses the SQL Procedure Pass-Through Facility. The second method uses the new SAS/ACCESS LIBNAME statement to accomplish the same task in an easier and more direct way.

Combining a PROC SQL View with a SAS Data Set By Using the Pass-Through Facility

This example joins SAS data with CA-OpenIngres data that is retrieved by using a Pass-Through query in a PROC SQL SELECT statement.

In this example's PROC SQL CONNECT statement, the database name is textile and it is located on a remote network node named atlanta; the CA-OpenIngres server is specified by star.

Information on student interns is stored in the SAS data file, DLIB.TEMPEMPS. The CA-OpenIngres data is joined with DLIB.TEMPEMPS to determine whether any of the student interns have a family member who works in the CSR departments.

To join the data from DLIB.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 ingres 
   (database='atlanta::textile/star');
%put &sqlxmsg;

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

disconnect from ingres;
quit;

Note: When SAS data is joined to DBMS data by using 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, as shown in the next example. Another way to increase efficiency is to extract the DBMS data and place it in a new SAS data file, assign SAS indexes to the appropriate variables, then to join the two SAS data files. [cautionend]
Output for both of these examples is shown in Combining a PROC SQL View with a SAS Data Set.

Combining a PROC SQL View with a SAS Data Set By Using a SAS/ACCESS LIBNAME

This example creates a PROC SQL view, MYSASLIB.EMP_CSRALL, from the DB2 table EMPLOYEES and joins the view with a SAS data set to select only interns who are family members of existing employees.

libname mydb2lib db2 ssid=db2;
libname mysaslib "sas-data-library";
title 'Interns Who Are Family 
       Members of Employees';

 create view mysaslib.emp_csrall as
 select * from mydblib.employees
  where dept in ('CSR010', 'CSR011', 'CSR004');

proc sql;
  select tempemps.lastname, tempemps.firstnam, 
         tempemps.empid, tempemps.familyid, 
         tempemps.gender, tempemps.dept, 
         tempemps.hiredate
    from mydb2lib.employees as emp, 
         mysaslib.tempemps as temps
    where emp.empid=temps.familyid;

quit;

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

Chapter Contents
Previous
Next
Top of Page