Hello there!
I came across a problem connecting SAS to PostgreSQL via SAS ACCESS to ODBC.
I had some drivers in <sashome>/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver, but psqlodbcw.so and psqlodbca.so returned data in UTF-8, but I need ANSI. SAS accept them. After I installed the new postgresODBC drivers https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8.5-x86_64/postgresql13-odbc-13.02.0000...
But SAS refuses to accept them:
"""ERROR: CLI error trying to establish connection: [unixODBC] could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket '/tmp/.s.PGSQL.5432'? """
The request via console isql/iusql passes normally, odbc.ini is the same. isql/iusql returns both in UTF-8 or ANSI, but SAS refuses to work.
Parameters:
System: x86_64 RHEL 8.6
Session encoding SAS - KZ1048,
Encoding DB - UTF-8,
Postgres DB - v13.6 on a remote server x86_64 RHEL 8.5.0-4
unixODBC - v2.3.4
Which version of postgresODBC is better to use, or how to solve the problem of getting data in ANSI?
Changing the encoding of the SAS session, changing the encoding of the database, using DataDirect is not suitable yet((
It turned out to solve the connection problem by using the "noprompt" parameter in assign library.
These properties can be added to Other Options in SAS MC in the library properties.
Example:
LIBNAME <NAME> ODBC noprompt="uid=<login>;pwd=<password>;dsn=<name in odbc.ini>;server=<db host name>;database=<name of db>;" <other default assign options>;
Have you made the required configuration changes for the new ODBC driver:
"You might have to use a text editor to modify the odbc.ini file in your home directory to configure
data sources. Some ODBC driver vendors may allow a system administrator to maintain a centralized
copy by setting an environment variable.
The ODBC drivers are ODBC API-compliant dynamic link libraries, referred to in UNIX as shared
objects. You must include the full path to the dynamic link libraries in the OS load library
environment variable, i.e., LD_LIBRARY_PATH, LIBPATH, or SHLIB_PATH, so that the ODBC drivers
can be loaded dynamically at run time."
I suggest you open a Tech Support track for your problem if you haven't done so already as they are in the best position to help.
1.The drivers in <sashome>/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/1. Driver are for use of SAS Infrastructure Data Server which is incidenally is PostgresQL
The error
""""ERROR: CLI error trying to establish connection: [unixODBC] could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket '/tmp/.s.PGSQL.5432'? """
clearly indicates that SAS is unable to connect to the remote PostgreSQL database.
As you are able to connect using ISQL, the error is most likely due ot some error in configuration. Check if you have a postgresql-odbc client installed on the SAS Application Server and it is configured appropriately.
It turned out to solve the connection problem by using the "noprompt" parameter in assign library.
These properties can be added to Other Options in SAS MC in the library properties.
Example:
LIBNAME <NAME> ODBC noprompt="uid=<login>;pwd=<password>;dsn=<name in odbc.ini>;server=<db host name>;database=<name of db>;" <other default assign options>;
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
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.