Date:         Tue, 4 Nov 2003 09:25:38 -0500
Reply-To:     "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Subject:      Re: Automated Oracle query from SAS
Content-Type: text/plain; charset="iso-8859-1"

Here's an example of using PROC SQL to access Oracle tables via a pass-thru
query ( i.e. Oracle does all the work and passes the result set back to SAS
):

proc sql ;
  connect to oracle(user     = "&user"     orapw = "&password"
                    buffsize = &buff       path  = "&path"
                    preserve_comments);

  create table credit_card_sas_dataset as
                select * from connection to oracle
                   (
                   select name, address, phone, card_num, interest_amt
                           from credit_card
                    where interest_amt > 10
                   );

  disconnect from oracle;
quit;

See the Online Docs for the EXECUTE (  ) by Oracle clause as well, it'll
allow you to create, drop etc.. Oracle tables, essentially execute any valid
Oracle SQL command.

It's also possible to use the Oracle keyword in the SAS libname statement
and refer to Oracle tables like you might to a native SAS dataset.  Note
that when sub-setting, the pass-through method above is more efficient since
not every row is brought into SAS to be examined ( as would occur below ).

libname dseprod oracle user=&user pass=&password path=&path
       buffsize=&buff direct_sql=nowhere ;

data sas_dataset;
  set dseprod.credit_card ( keep = card_num, interest_amt );
    where interest_amt > 10;
  etc..
run;

                -----Original Message-----
                From:   Andras Cser [mailto:szemeteslada@YAHOO.COM]
                Sent:   November 3, 2003 11:23 PM
                To:     SAS-L@LISTSERV.UGA.EDU
                Subject:        Automated Oracle query from SAS

                All,
                I am a newbie with SAS, but need to learn how to run an
Oracle query
                every night against an Oracle database. If anyone has some
SAS code I
                could learn from,
                it would be sooo nice.
                Thanks,
                Andras