topic Re: How to connect to oracle from sas to pull data from oracle? in ODS and Base Reporting

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

Petersas — Mon, 21 May 2012 14:17:41 GMT

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.

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

Haikuo — Mon, 21 May 2012 14:40:09 GMT

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

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

Doc_Duke — Mon, 21 May 2012 15:34:46 GMT

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

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

Petersas — Mon, 21 May 2012 18:42:37 GMT

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

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

Doc_Duke — Mon, 21 May 2012 19:02:14 GMT

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

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

Petersas — Tue, 22 May 2012 07:42:30 GMT

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.

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

Doc_Duke — Tue, 22 May 2012 12:50:30 GMT

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#p1ujrhdoe1p743n12awcf7mwyg81.htm

Please provide the log of what you tried.

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

Linlin — Tue, 22 May 2012 12:56:42 GMT

using libname:

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

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

Petersas — Fri, 02 Nov 2012 16:09:51 GMT

Thanks for your replies... Issue resolved.