Date:         Tue, 18 Sep 2007 22:41:38 -0700
Reply-To:     beatrice <beatrice@PROMED.CO.ZA>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         beatrice <beatrice@PROMED.CO.ZA>
Organization: http://groups.google.com
Subject:      Proc SQL - selecting from oracle and exisiting dataset
Comments: To: sas-l@uga.edu
Comments: cc: beatriceghitoiu@yahoo.com
Content-Type: text/plain; charset="iso-8859-1"

Hi,

I need to do an inner join between a table in Oracle, thus im using a
Connect to Oracle statement, and an exisitng dataset in a Work
library.  Here is the code:


PROC SQL NOPRINT;

                CONNECT TO ORACLE       (USER = &unouser.
                                                 PASS = &unopass.
                                                 PATH = &unonode.
                                                 BUFFSIZE = 32767
                                                 );

                CREATE TABLE All_UNO_CPF_Accounts_&mmmyyyy. AS

                SELECT * FROM CONNECTION TO ORACLE

                        (SELECT                                                                                                 ALL.CPF_ADMIN_FACILITY_FEE,

MLOAN.TERM

                        FROM            UNO.MORTGAGE_LOAN_MONTHLY                                                       PARTITION
(information_date_&mmmyyyy.) MLOAN,


All_UNO_CPF_Accounts1_&mmmyyyy.  ALL

                        WHERE   ALL.ACCOUNT_NUMBER = MLOAN.ACCOUNT_NUMBER                                       );

DISCONNECT FROM ORACLE;
QUIT;

im not sure how to do this inner join if the ALL table is not in the
same oracle connection that im using for the MLOAN table..

Please help with any suggestions,

Thanks,
Beatrice