LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 1998, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


Back to: Top of message | Previous page | Main SAS-L page