The SAS Output Delivery System and reporting techniques

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

Reply
Occasional Contributor
Posts: 10

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

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.

Respected Advisor
Posts: 3,156

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

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

Trusted Advisor
Posts: 2,116

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

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

Occasional Contributor
Posts: 10

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

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.

Trusted Advisor
Posts: 2,116

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

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.

Super Contributor
Posts: 1,636

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

using libname:

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


Occasional Contributor
Posts: 10

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

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

Trusted Advisor
Posts: 2,116

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

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

Occasional Contributor
Posts: 10

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

Thanks for your replies... Issue resolved.

Ask a Question
Discussion stats
  • 8 replies
  • 1733 views
  • 0 likes
  • 4 in conversation