Setting up SAS Access connection to PostgreSQL - Windows Env

[Yaswantha001]

Hi,

Please request your help on setting up PostgreSQL connection via SAS Accessand we do not want to set-up using ODBC connection.

In regards to the same, Do we need to install PostgreSQL client software in SAS servers and any change required in any of the configuration files to update the PostgreSQL server details.

Note: SAS 9.4  on Windows Env 

Appreciate your help on this.

Thank you. 

[SuryaKiran]

First thing you need to check is whether you have Licence to SAS/Access interface to PostgreSQL.

You can do this by running 

PROC SETINIT;

RUN;

If you don't have licensed then use ODBC. My suggestion is using ODBC connection and avoiding new setup connection hazels.   

[Yaswantha001]

Thank you. We have License and we don't want to connect via ODBC. As mentioned, I am looking for some suggestions as mentioned earlier. 

[LinusH]

Question is, didn't you check the requirements prior to purchasing the SW?
"By default, the ODBC driver for PostgreSQL is shipped with BASE SAS. When SAS/ACCESS Interface to PostgreSQL code is submitted, this ODBC driver will automatically be referenced. As a result, it is not necessary to set environment variables to point to this ODBC driver. With SAS/ACCESS Interface to PostgreSQL, you can either provide connection specifics in your code or reference a DSN. If the DSN method is used, you must create a serviceable ODBC.INI. You may also need to set the ODBCINI environment variable to point to it."

And what do you base your requirement on? The SAS web infrastructure platform uses PostgreSQL as "internal" storage as well, and for that uses JDBC which for me is same-same.

[SimonDawson]

What you need to have deployed on the system to have the SAS/ACCESS Interface to PostgreSQL access product work are documented in this system requirements documentation.

http://support.sas.com/documentation/installcenter/en/ikfdtnwx6sr/66390/PDF/default/sreq.pdf

You just need to ensure the client libraries location is included in the PATH environment variable.

There is no configuration updates required in the SAS configurations to get this access product to work.

[Yaswantha001]

@SimonDawson

Thank you Simon and Can you please share some details and as I am very clear what to do from SAS SMC side. But I am still not sure, on how to set-up connection from Server front.

How do we ensure, our SAS servers can interact/recognise the Third Party Postgre SQL server and this is can achieved only when we install and configure the PostgreSQL. Please correct if I am wrong and this is where I am not clear on how to do it. 

[SimonDawson · Accepted solution]

@Yaswantha001

Sorry it's taken me a while to spot that you replied to this thread.

Once you have a libname statement working inside SAS Foundation on the server you would then set about defining Metadata to allow users to access this library from the SAS clients. The Configuration Guide for UNIX covers how to get SAS Foundation setup to get the library assigned but once that is done you'd need to look into the SAS 9.4 Intelligence Platform: Data Administration Guide. This manual covers how to define library metadata with SAS Management Console. This manual has a section "Connecting to Common Data Sources" it walks you through how to setup some common data sources with some screenshots included too.

Did you manage to get this all sorted?

EDIT -Add URL to documentation