Date:         Wed, 15 Apr 1998 01:17:00 GMT
Reply-To:     LPogoda <lpogoda@AOL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         LPogoda <lpogoda@AOL.COM>
Organization: AOL http://www.aol.com
Subject:      Re: joining sas dataset with oracle table

In article <s5333ca3.028@smtplink.sdps.org>, Barbara Okerson
<TNPRO.BOkerson@SDPS.ORG> writes:
 
>Running SAS 6.11 on Windows 3.11 and Unix.., Oracle on the Unix
>machine.
>
>I have a SAS dataset that is a list of patients with particular diagnoses.  I
>need to merge this set with an Oracle database to get additional
>diagnoses for this set of patients.  All the variables in the SAS dataset
>are present in the Oracle database.
>
>In all the pass-through, sql, etc. examples I have, oracle tables are only
>merged with oracle tables and then converted to SAS datasets.  Do I
>have to create an Oracle table from my SAS data set before I merge it
>with Oracle?  Or is it possible to include both the SAS and the Oracle in a
>merge?
>
>Thanks in advance.
>
>
 
You have several options.  First, and perhaps the most clumsy, you can have
your SAS program write and execute an ORACLE script which extracts rows from
the appropriate ORACLE table(s) to a flat file or files, which you can then
read into SAS.  Second, you can use PROC DBLOAD to load the SAS dataset to
ORACLE as a table, write pass-thru for ORACLE to do the join and send the
result back to SAS.  Third, you can set up a SAS view on the ORACLE table, then
use the view in a MERGE statement as though it was any other dataset.
 
Of these alternatives, I'd probably choose the second.  The first requires too
much work on the programmer's part and you have to coordinate the timing among
SAS, UNIX, and ORACLE.  Using the third alternative is simple, but when you do
the merge in SAS the entire ORACLE table is first exported to SAS, the merge is
done, and any excess ORACLE rows are thrown away.  If the ORACLE table is
small, that's not too bad, but if it's in the millions of rows, the whole
process consumes a lot of resources.  PROC DBLOAD is relatively
straightforward, and much as it pains me to say it, ORACLE joins on properly
indexed tables goes pretty fast.
 
SQL in ORACLE, to my knowlege, works only on ORACLE tables - you can't join a
table and a SAS dataset or flat file in ORACLE.