topic Re: how to add postgresql to sas managment console? in New SAS User

how to add postgresql to sas managment console?

Dawid3 — Fri, 15 Apr 2022 06:59:04 GMT

Hi,

I m new in sas, and I have, a problem with conection sas managment console to postgresql base. When I try to register tables, program returns this error:

ERROR: CLI error trying to establish connection: could not connect to server: No such file or directory
locally and accepting
ERROR: Error in the LIBNAME statement.
and this error:

ERROR: CLI error trying to establish connection: could not connect to server: Connection timed out
"IP" and accepting
ERROR: Error in the LIBNAME statement.

When I try to connect by odbc program returns this error:

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found and no default driver
specified
ERROR: Error in the LIBNAME statement.

Somebody knows how to solution this problem or have instruction how to connect sas to postgresql?

Re: how to add postgresql to sas managment console?

Sajid01 — Fri, 15 Apr 2022 12:22:35 GMT

Looks like the connection to Postgresql connection has not been setup.

Your SAS and OS Administrators should be of help.

Re: how to add postgresql to sas managment console?

SASKiwi — Sun, 17 Apr 2022 00:09:12 GMT

Has the data library definition for Postgresql been created by you or by someone else? If it is by someone else then as @Sajid01 has said your SAS administrator should be able to help. If you are defining the connection, the best way to make sure it is correct is to try it out in a LIBNAME statement first.

Re: how to add postgresql to sas managment console?

Dawid3 — Tue, 19 Apr 2022 05:37:11 GMT

When i run this code:

proc setinit noalias; run; %put _all_; proc product_status; run;

program returns this:

---SAS/ACCESS to Postgres

For SAS/ACCESS to Postgres ...
Custom version information: 9.4_M7

Re: how to add postgresql to sas managment console?

SASKiwi — Tue, 19 Apr 2022 22:38:56 GMT

The original errors you posted indicated the correct software is installed, but the SMC data library definition is incorrect. So still need the answers to the questions in my previous post.

Re: how to add postgresql to sas managment console?

Sajid01 — Tue, 19 Apr 2022 23:18:32 GMT

Hello @Dawid3
The output of the proc setinit indicates that you have the SAS Access interface to Postgres.
From a reading of your first it indicates that SAS is on a Unix Server.

You have said that you are new to SAS. Therefore I would suggest the fhe following.
As suggested in my earlier post I would suggest that if you have a SAS Administrator, do take his assistance. Otherwise somebody already working at SAS at your place. They would be having a better understanding of your environment.

Your post lists the following

ERROR: CLI error trying to establish connection: could not connect to server: No such file or directory
locally and accepting
ERROR: Error in the LIBNAME statement.

The first indicates that a connection could not be established to the server. Under such circumstances one should find out why it could not be established.
The second error is a natural outcome of the first.

As a first step one has to check if a connection can be established using a libname statement. If that works you can create a database connection in the SMC.

It appears that you are in the SAS is installed in the Unix/Linux environment, you can open the SAS session in the command line with -nodms option and run the following statement.

libname test  postgres server=your_server_name port=5432 
   user=username  password='password' database=database_name;

(Alternatively the above command can be run in the SAS Enterprise Guide or SAS Studio or the SAS Windowing environment.)
If everything works, there should be no error in the log and you will see that the libname has been set.
If this step is OK, then read the following and see if a connection has been configured correctly in the SAS Management Console.

https://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/
https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2357-2018.pdf

In the event the libname statement fails, please post the log with details about your SAS environment.

Re: how to add postgresql to sas managment console?

Patrick — Tue, 19 Apr 2022 23:46:23 GMT

As @SASKiwi already proposed try first to issue a libname out of SAS code before diving into a metadata definition for such a libname.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0nc2tikruabp6n1008nkdf0buee.htm

Re: how to add postgresql to sas managment console?

Dawid3 — Mon, 25 Apr 2022 13:12:39 GMT

hi, the problem was with postgre, and more precisely, in a file called pg_hba.conf, you had to correctly set up the allow replication connections from the host section.

Re: how to add postgresql to sas managment console?

Sajid01 — Mon, 25 Apr 2022 14:17:48 GMT

Was your issue resolved?
The fact that the pg_hba.conf had to be updated/modified indicates that the PostgreSQL server was not properly configured.
This files has configuration settings which in simple terms define who can connect and from where one can connect.
This explains the cause of the error
ERROR: CLI error trying to establish connection: could not connect to server: No such file or directory
locally and accepting.

Re: how to add postgresql to sas managment console?

Dawid3 — Mon, 25 Apr 2022 16:26:49 GMT

Yes, my issue is solved, it was about what you've said. After modyfing the file everything works perfectly

Re: how to add postgresql to sas managment console?

SASKiwi — Mon, 25 Apr 2022 20:07:27 GMT

@Dawid3 - Please update your post as answered in that case.