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.
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
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
I did it. Same problem it says "ODBC" driver can not found.
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
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.
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
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
Thank you I will try all the options and update
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.