266 Combining a PROC SQL View with a SAS Data Set Chapter 14
title ’Brief Data for All Invoices’;
select * from samples.brief
order by billedon, invnum;
quit;
The output from the Samples.Brief view is the same as shown in Output 14.1 on
page 265.
When a PROC SQL view is created from a Pass-Through query, the query’s DBMS
connection information is stored with the view. Therefore, when you reference the
PROC SQL view in a SAS program, you automatically connect to the correct database,
and you retrieve the most current data in the DBMS tables.
Combining a PROC SQL View with a SAS Data Set
The following example joins SAS data with Oracle data that is retrieved by using a
Pass-Through query in a PROC SQL SELECT statement.
Information on 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;
Output for this example is shown here.
Output 14.2 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
