SAS ODBC connection

In this article we will see SAS-Oracle integration or “how to access any Oracle database from SAS?”. Here is our main purpose is to create below libname statement to access oracle library.

LIBNAME Library_name ORACLE  PATH=sid_name  SCHEMA=”schema name”  USER=user_name  PASSWORD=”password”;

There are some optimization techniques as well to improve performance between SAS-Oracle connections.

Steps for SAS ODBC/Oracle Integration

Step 1 –

For Database set up, 1st thing one need to do is getting server details in below format which we received form NL Database team

MCIPRD=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port_number)))(CONNECT_DATA=(SID=sid_name)(SRVR=DEDICATED)))

Step 2 –

You need to add this database details in ‘tnsnames. ora’ file located on path /oracle/ora11g/product/11.1.0/client_1/network/admin with the help of DBA team.

We don’t have access to change this file so we need to take help from DBA team

Step 3 –

Once Step 2 is ready, we need to configure the new server on SAS Management console.

Right click on ‘Server Manager’ below window will pop up

OracleSAS1

Click on ‘New server’ and select ‘Oracle server’ from the list

OracleSAS2

Click ‘Next >’

Give name of the server and click ‘Next >’

Now select the server which is mentioned in ‘tnsnames.ora’ file as below

OracleSAS11

Click ‘Next >’ two times below window will pop up
Click on ‘Finish’

Step 4 –

After doing a server configuration, next step is to create group as with below steps.

Right click on ‘User manager’ -> ‘New’ -> ‘Group’

OracleSAS5

Go to ‘Members’, add ‘Netherland Users’ as below

Nothing in ‘Groups and Roles’.

Now in ‘Accounts’ , click on ‘New’.

Here you have to add credetials where it has permission to access Database server of NL.


OracleSAS7

One can get information from respective data base team regarding this credentials.

Click ‘Ok’ twice and you have new group named ‘NL Oracle Users’.

Step 5 –

Now next step is to create oracle library for server which we created in ‘step 3’.

Right click on ‘Library’ -> ‘New library’ as below

OracleSAS8

Select ‘oracle’ Library from the popped up menu as below.

OracleSAS9

Click ‘Next’.

It will ask for ‘Name’ for the library. For example ‘NL Oracle Lib’

In ‘Next’ menu select server which is ‘SASApp’ click ‘Next’ and in next menu give ‘Libref’ as ‘Name’ of library.

Now in ‘Next’ menu, one need to give ‘Database Schema name’ as mentioned in tnsnames.ora file and select ‘Connection’ as server we created in ‘STEP 3’

OracleSAS10

Click on ‘Next’ and ‘Finish’.

New data base library created for you.


Leave a Reply

© 2017 Database ETL. All rights reserved.