topic Estabilishing an Oracle DB connection in Base SAS in SAS Programming

Estabilishing an Oracle DB connection in Base SAS

bnarang — Fri, 25 Apr 2014 07:41:55 GMT

Hi Guys

Can you please help me with this problem. I need to setup my Base SAS to connect with oracle database connection. I have access to Database and connect it via PL/SQL developer but I want to do the same using SAS. I have tnsname.ora file from where I can lookup the details required for access.

In terms of modules, I have SAS/Access to Interface PC files, So, I believe can access via ODBC ( Have very less technical knowledge in terms of connections and all)

Can you please help me what script would be required and Do I need to setup my system in terms of adding ODBC driver??

Many Thanks in Advance

Regards

Re: Estabilishing an Oracle DB connection in Base SAS

jakarman — Fri, 25 Apr 2014 08:23:17 GMT

You could possible try accessing Oracle by using an generic ODBC connection. This could work when you have the SAS license Access/ODBC.

It will probably not having any support to normal Oracle specifics.

The more advanced approach is having the Oracle-client installed on your machine. You already have this as PL/SQl developer

It will require the SAS/Access Oracle license and the related software being installed.

You can check what is licensed in SAS by running: " proc setinit; run; "

Re: Estabilishing an Oracle DB connection in Base SAS

bnarang — Fri, 25 Apr 2014 08:57:58 GMT

Thanks

I don't have access to ACCESS FOR ORACLE. I am looking for a script which I can use/modify and edit my own configuration to connect. Can you please help me with that.

Regards

Re: Estabilishing an Oracle DB connection in Base SAS

jakarman — Fri, 25 Apr 2014 09:55:01 GMT

You cannot change your own configuration to do something that is not there, sorry

If you have SAS/Access odbc licensed you could try the ODBc definitions getting to Oracle, but remember it is limited.

Assuming you are using SAS/Base Windows the challenge is in configuring the ODBC Windows connection. Just try that (when ODBC is licensed)

The last options is transferring your data using CSV files as intermediate data from you oracle PL/SQL connection. Not very advanced and needing the handcraft.

Re: Estabilishing an Oracle DB connection in Base SAS

bnarang — Fri, 25 Apr 2014 10:04:42 GMT

I think I am not able to convey the message to you. I am looking for a SAS SCRIPT for Oracle loggin ( Which I believe would be generic and similar to how we access to SQL Server). This, I can add my own database name and can try to run. I am looking for a generic script!

Re: Estabilishing an Oracle DB connection in Base SAS

jakarman — Fri, 25 Apr 2014 10:15:38 GMT

bnarang, there is no SAS scritp for that. There are Libname statement options user/pswd) and SQL pst methods both that are bound to sas/access software.

What you first need to do as you have no SAS/Access Oracle is to verify you can access oracle in the same way as SQL-server using the Windows ODBC drivers.

That is a configuration task in Windows not SAS. SAS(R) 9.3 Drivers for ODBC: User's Guide, Second Edition

Ah that was the drivers to use SAS as a DBMS server

44774 - System Requirements for SAS/ACCESS® 9.3 Interface to Greenplum in Windows environments is given the information dsn usage (odbc) to sqlserver and more.

When you can define an oracle connection with a dsn (windows scritping) ODBC you could reach Oracle