topic Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment in Administration and Deployment

Need help in connecting Postgres database to PC SAS 9.4 Windows environment

hukesh — Wed, 12 May 2021 11:56:48 GMT

Hi,

I've SAS 9.4 in my personal windows PC and trying to connect to Postgres database (aws storage) to access tables/datasets in the database. I would like to perform some analysis in SAS with that data. I tried creating library with libname and have below message.

ERROR: The POSTGRES engine cannot be found.

ERROR: Error in the LIBNAME statement.

Also, I checked whether I have access to Postgres in SAS (PROC SETINIT; RUN;) and I see that I've access to Postgres, ODBC etc. And then I tried PROC PRODUCT_STATUS; RUN; Here I don't see Postgres though.

I found this in one of the community posts " It's possible that you have SAS/Access to Postgres licensed but it is not installed on the server". I think, I'm in same situation. Could someone suggest me how to install it?

I don't have any server though. Just trying to connect to database from simple PC SAS on a personal laptop.

Thanks,

Hukesh

Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment

Ksharp — Wed, 12 May 2021 12:41:51 GMT

Did you try ODBC connection ?

Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment

Sajid01 — Wed, 12 May 2021 13:32:04 GMT

Hello
From your question what I understand is that you have SAS installed on your Windows PC and PostgreSQL installed on a remote machine (AWS).
Under this scenario, you need to establish an odbc connection. One option is to use the Microsoft ODBC Data Source Administrator . You will need a PostgreSQL odbc driver installed.

On PC SAS this would be my preferred method.
If you want to use SAS Access Interface to PostgreSQL please see here https://documentation.sas.com/doc/en/calcdc/3.4/dplyml0phy0dkr/n0zpnn9s53x8m9n1m22gfkf8f6eu.htm.

This later approach would be good for Server.

Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment

SimonDawson — Thu, 13 May 2021 01:02:04 GMT

Looks like you are in a situation w here you have SAS/ACCESS Interface to PostgreSQL licensed but it is not installed. How to add products to an existing SAS 9.4 installation is documented here.

Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment

hukesh — Thu, 13 May 2021 19:56:05 GMT

Hi,

Thank you very much! I was able to establish connection using ODBC. Library is created and I see the data sets. But, data is not fetching. All datasets are showing zero observations. Any suggestions?

I've installed ODBC Data source administrator and CData ODBC driver for PostgreSQL. Any alternatives?

Regards,
Hukesh

Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment

Ksharp — Fri, 14 May 2021 12:06:53 GMT

Did you try run a simple data step?

libname x odbc dsn=.............;
data have;
set x.PostgreSQL_table ;
run;

Re: Need help in connecting Postgres database to PC SAS 9.4 Windows environment

Sajid01 — Sat, 15 May 2021 11:59:54 GMT

Hello @hukesh
Under your situation, i would try to install pgAdmin on the Desktop and try connecting to the PostgreSQL.
Then we can see if there is data in the database or not.