BookmarkSubscribeRSS Feed
aquadri
Obsidian | Level 7

Hi,

I am unable to create a libname pointing to sas odbc.

 

sas code:

libname test odbc dsn=sas;

 

getting following error message:

 

CLI error trying to establish connection: Unable to retrieve error message.

Error in the LIBNAME statement

 

Tried with different names for the datasource.

10 REPLIES 10
SASKiwi
PROC Star

Why are you trying to read SAS data from SAS with ODBC when SAS can read it directly?

libname test 'c:\MySASDataFolder';
aquadri
Obsidian | Level 7

Hello Friend,

 

Thank you. I have to use Power BI on my PC.

 

AQ

SASKiwi
PROC Star

To read SAS data through an ODBC connection? If so you would be better off exporting your SAS datasets to SQL Server tables then use PowerBI with those. This is a much more efficient approach and is one we use ourselves.

aquadri
Obsidian | Level 7

Yes, that can be done. Than I will have to keep running an additional job that refreshes the data on SQL Server/ Oracle whenever SAS data is changed. Moreover, I do not want two have to copies of one data. If ODBC can work that will be good.

SASKiwi
PROC Star

I haven't tested that possibility with PowerBI. Accessing SAS data on your PC this way with just the SAS ODBC driver will only work if you have a complete SAS installation on your PC as well. If you want to read remote SAS server data then SAS/SHARE has to be installed and licensed as well. We needed to use the second scenario and didn't want to get SAS/SHARE just to do it.

aquadri
Obsidian | Level 7

Thank for trying and testing the SAS ODBC. 

 

Our data is actually sitting on remote Linux server. We access the data on local PC SAS9.4 through remote libnames. We do have sas/Share also installed on the Linux servers . In addition to using PC SAS for ODBC, I also tried creating a odbc link through SAS/Share, but  that didn't work either.

 

Interestingly, we do have one old windows server with sas 9.1. we have SAS oDBC installed on it. We have the exact same ODBC data source created on it connecting through SAS/SHARE and that link works from that old windows server.

 

Please see the attached images.

 

 

 

 

SASKiwi
PROC Star

I suggest you progress this by opening a track with SAS Tech Support. It sounds like you have all the right software, probably just some extra configuring is required. I'm not in a position to test this myself as we don't have SAS/SHARE.

aquadri
Obsidian | Level 7

Yes, I think I should do that.  Thank you. Appreciate your help.

Tom
Super User Tom
Super User

@aquadri wrote:

Hi,

I am unable to create a libname pointing to sas odbc.

 

sas code:

libname test odbc dsn=sas;

 

getting following error message:

 

CLI error trying to establish connection: Unable to retrieve error message.

Error in the LIBNAME statement

 

Tried with different names for the datasource.


I don't get it.

What is the definition of the dsn named SAS that you are trying to connect to with this SAS statement?

Note you can use the Insert Photos icon in the forum editor to insert photos instead of hiding them inside of an attached PDF file.

ankitd
Obsidian | Level 7

Have you defined the ODBCHOME, ODBCINI and ODBCINST path as well LD_LIBRARY_PATH.

 

The best validation is to verify whether the isql test works fine, then the connectivity from your compute machine and database is not correct, this would rule out issue in SAS.

 

If the isql test works then you need to look at the configuration setup for database.

 

Also there are different libname options to connect to db using odbc (apart from dsn), have you tried those, does any of those work?

 

Worth trying the above before we can look at other possibilities.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 3602 views
  • 0 likes
  • 4 in conversation