Unable to create libname for sas 9.4 64-bit odbc

[aquadri]

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.

[SASKiwi]

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

libname test 'c:\MySASDataFolder';

[aquadri]

Hello Friend,

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

AQ

[SASKiwi]

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]

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]

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]

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]

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]

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

[Tom]

---

@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]

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.