BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
learner_sas
Quartz | Level 8

Hello Everybody,

I am trying to use libname statement to connect sas to oracle below is the code I ran;

libname myoralib oracle user=userone password=XXXXXXX path=pathone; /* pathone is name of system dsn name for ODBC driver for oracle. My pathone does not have any characters not allowed in sas naming convention*/

but I got error saying oracle saying

ERROR: The ORACLE engine cannot be found.

I saw all of the comments and updates but still not able to figure out.

I am wondering if anybody has ran to same problem like me.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

Here are the steps that I use for ODBC:

Step 1 must be done on your SAS environment.  If your SAS runs on a different Windows machine, the data source must be defined there as a DSN.  If you're running everything local (SAS and SAS Enterprise Guide are on your local PC), then you can configure all of this on your machine.

1. Use Windows Data Sources (ODBC) to define a System DSN for your Oracle data source.  This requires an Oracle ODBC driver.  I have the Oracle in instantclient_12_1.  Be sure to test the connection in the Data Sources facility.  If using the 64-bit version of SAS, you'll need the 64-bit Oracle client and you'll define this DSN in the 64-bit Data Sources facility.

Step 2 can be done from SAS Enterprise Guide, connected to your SAS server.

2. In your SAS program, use a libname statement like:

   libname mylib odbc datasrc="YOURDSN" USER="userID" password="Password" ;

As Jaap points out, you don't have SAS/ACCESS to ODBC so you'll need to pursue OLE DB instead.  I recommend similar steps for testing the connection outside of SAS code.  You can try it with File->Open->OLEDB in SAS Enterprise Guide, but be aware that direct access to the database from SAS Enterprise Guide is not an efficient method for accessing that data in your SAS program.

If you're concerned about the password in open code, you can try the techniques in this article:

Five strategies to eliminate passwords from your SAS programs

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

10 REPLIES 10
Haikuo
Onyx | Level 15

There may be more problems, but as error message suggested:

You have: "/* pathone is name of system dsn name for ODBC driver for oracle. ", but you attempted to use Oracle engine in your libname statement:

"libname myoralib oracle ". Try to replace 'oracle' with 'ODBC', see if it works.

Haikuo

learner_sas
Quartz | Level 8

I did it. Same problem it says "ODBC" driver can not found.

Haikuo
Onyx | Level 15

The syntax is a little off, it generally goes like:

libname mydblib odbc datasrc=orasrvr1 user=testuser password=testpass;

While orasrvr1 is your DSN. I have never seen "path=" in ODBC engine, it was seen in Oracle engine though.

That being said, make sure you have the ODBC driver for Oracle (version may vary) installed and corresponding DSN configured in ODBC data source administrator (Windows).

Good Luck,

Haikuo

jakarman
Barite | Level 11

Do a "proc setinit; run; "  As it looks that either you are not licensed to the mentione SAS/ACCess software or the installation is done wrongly.

---->-- ja karman --<-----
learner_sas
Quartz | Level 8

after running proc setint;run;Output as follows. It says SAS/Access for two of the products. Do I need more for Oracle connection.

Original site validation data

Site name:    company name

Site number: a random number

Expiration:   29JUN2015.

Grace Period:  45 days (ending 13AUG2015).

Warning Period: 45 days (ending 27SEP2015).

System birthday:   27OCT2014.

Operating System:   WX64_WKS.

Product expiration dates:

---Base SAS Software                                                                                    29JUN2015 

---SAS/STAT                                                                                             29JUN2015 

---SAS/GRAPH                                                                                            29JUN2015 

---SAS Enterprise Miner                                                                                 29JUN2015 

---SAS Enterprise Guide                                                                                 29JUN2015 

---SAS/ACCESS Interface to PC Files                                                                     29JUN2015 

---SAS/ACCESS Interface to OLE DB                                                                       29JUN2015 

---SAS Enterprise Miner for Desktop                                                                     29JUN2015 

---SAS Workspace Server for Local Access                                                                29JUN2015 

---SAS Add-in for Microsoft Excel 

Thank you

ChrisHemedinger
Community Manager

Here are the steps that I use for ODBC:

Step 1 must be done on your SAS environment.  If your SAS runs on a different Windows machine, the data source must be defined there as a DSN.  If you're running everything local (SAS and SAS Enterprise Guide are on your local PC), then you can configure all of this on your machine.

1. Use Windows Data Sources (ODBC) to define a System DSN for your Oracle data source.  This requires an Oracle ODBC driver.  I have the Oracle in instantclient_12_1.  Be sure to test the connection in the Data Sources facility.  If using the 64-bit version of SAS, you'll need the 64-bit Oracle client and you'll define this DSN in the 64-bit Data Sources facility.

Step 2 can be done from SAS Enterprise Guide, connected to your SAS server.

2. In your SAS program, use a libname statement like:

   libname mylib odbc datasrc="YOURDSN" USER="userID" password="Password" ;

As Jaap points out, you don't have SAS/ACCESS to ODBC so you'll need to pursue OLE DB instead.  I recommend similar steps for testing the connection outside of SAS code.  You can try it with File->Open->OLEDB in SAS Enterprise Guide, but be aware that direct access to the database from SAS Enterprise Guide is not an efficient method for accessing that data in your SAS program.

If you're concerned about the password in open code, you can try the techniques in this article:

Five strategies to eliminate passwords from your SAS programs

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
learner_sas
Quartz | Level 8

Thank you I will try all the options and update

learner_sas
Quartz | Level 8

Hello Chris,

I was able to use OLE DB and connect SAS in my local machine to the tables in oracle servers. As you suggested my tables are really big and took really long time if I selected more than one table. I will explore my other options.

I always read your blog. You are awesome.

MarkL
Calcite | Level 5

Hi Chris, I realize this string is a bit old, but here goes, does the order of installation (SAS and Oracle client) matter?  At my site we have a couple hundred users that just got new 64-bit laptops with SAS 9.4 pre-installed.  Our IT department put the 32-bit Oracle client on all of these which of course does not work (error we are seeing is below.)  IT completed packaging a 64-bit Oracle client and engaged me for UAT.  They pushed it to my machine but I still get the same error.  I will start a new post/thread for this.  Thank you !!

 

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA found but not
loadable..
Please make sure Oracle environment is set correctly.
Look in the install/Config doc for additional info for your platform.
Other possible reasons - incomplete Oracle client install, 32/64-bit mismatch between Oracle
client & SAS, incorrect Oracle client version(Oracle client must match the version picked
during post-install process), incompatible sasora for your OS or its attribs don't permit
SAS to load it.
ERROR: Error in the LIBNAME statement.

jakarman
Barite | Level 11

There is no ODBC and no Oracle access licensed. You cannot use these ones.

You can used OLEDB and it should be possible to go to Oracle wiht that one.

See: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001361171.htm

There is an example how to connect orcle using this.

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 10654 views
  • 2 likes
  • 5 in conversation