How to connect to oracle from sas to pull data from oracle?

[Petersas]

Hi,

I'm newbie to SAS, trying to learn SAS myself. Can anyone let me know how to connect to oracle from SAS and pull data from tables to create report.

Any help will be appreciated.

[Haikuo]

Hi,

Please refer to this paper:

http://www.nesug.org/proceedings/nesug04/io/io04.pdf

In a nutshell, I usually use one of the following two approaches:

1. pass-through facility: It is believed that the query is done on Oracle side natively, so it could be more efficient.

2. Libname engine:

There are also other solutions such as proc dbload, which I have no experience in.

HTH,

Haikuo

[Doc_Duke]

Peter,

Note, particularly, the prerequisites at the bottom of page 1 in Haikuo's reference.  You need to have an oracle client piece on your machine and a license for SAS/Access for Oracle.

Your local IT support should be able to set you up with the Oracle client.  You should test the connection with SQL*Plus to make sure that you know that any problems you might see on the SAS side are not part of the underlying Oracle setup.

Doc Muhlbaier

Duke

[Petersas]

Oracle-client -- sqlplus- is installed on my station and i can run queries successfully from it. so how to get connected to oracle from SAS and get information from RDBMS tables into SAS for producing sas datasets.

[Doc_Duke]

Do you have SAS/Access to Oracle licensed and installed?  If so, what are the specific error message(s) that you are getting?

Did you read the documentation? it has examples.

http://support.sas.com/documentation/cdl/en/acreldb/63144/HTML/default/viewer.htm#p1ujrhdoe1p743n12a...

Please provide the log of what you tried.

[Linlin]

using libname:

libname test oracle user="***"   password="***"   path="***" schema=****;

[Petersas]

is PROC SQL; QUIT; used ONLY to get data from dbms table like oracle,mssql  or  used to manipulate data present in SAS dataset?

[Doc_Duke]

Many of us use it extensively for all sorts of data, including SAS datasets and DBMS tables.

[Petersas]

Thanks for your replies... Issue resolved.