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"
create table credit_card_sas_dataset as
select * from connection to oracle
select name, address, phone, card_num, interest_amt
where interest_amt > 10
disconnect from oracle;
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 ;
set dseprod.credit_card ( keep = card_num, interest_amt );
where interest_amt > 10;
From: Andras Cser [mailto:szemeteslada@YAHOO.COM]
Sent: November 3, 2003 11:23 PM
Subject: Automated Oracle query from SAS
I am a newbie with SAS, but need to learn how to run an
every night against an Oracle database. If anyone has some
SAS code I
could learn from,
it would be sooo nice.