topic Re: SAS9.4 decision manager postgres access through libname in SAS Data Management

SAS9.4 decision manager postgres access through libname

AhmedAccordBGro — Wed, 03 Jul 2019 11:46:09 GMT

Hi,

I have SAS 9.4, and I need to create a new schema on decision manager postgresql, to use it for data integration purposes.

I got my ODBC working, DSN entry, Libname working fine, test the connection is perfect, even I can see the table names during "Register Tables" wizard, BUT register table abort with error "ERROR: CLI describe error: E", and I got the same error in SAS Studio...

I'm attaching some snaps may be useful. Any thoughts please ??

Re: SAS9.4 decision manager postgres access through libname

JBailey — Wed, 03 Jul 2019 14:14:04 GMT

Hi @AhmedAccordBGro,

If I had to guess, my guess would be a character set issue; the key word is guess. In order to answer your question, we will need more information.

We need to see your specific LIBNAME statement.

Your entire SAS log (especially the entire error message). Try to keep it very simple.

Is your SAS server running on Linux or Windows?

The odbc.ini entry for your DSN.

Are you using an Easysoft driver?

Best wishes,

Jeff

Re: SAS9.4 decision manager postgres access through libname

AhmedAccordBGro — Thu, 04 Jul 2019 05:04:25 GMT

HI JBaily,

HYG..

SAS App Server:

OS: Red Hat Enterprise Linux Server release 7.6 (Maipo)

SAS: SAS 9.4 Rev. 940_18w47

DSN:

[PGS]
Driver=/sas/sashome/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/psqlodbcw.so
ServerName=sasem.servers.gov
schema=public
username=dcmdb
password=Passw0rd
db=dcmdb
port=10482
UseServerSidePrepare=1

LIB Stmnt:

LIBNAME PGLIB odbc DSN=PGS schema=public;

Log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 LIBNAME PGLIB odbc DSN=PGS schema=public;

NOTE: Libref PGLIB was successfully assigned as follows:

Engine: ODBC

Physical Name: PGS

75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

NOTE: (Susing SAS Studio) After lib definition, I can see all table names listed on the lift panel!

Then, I'm trying to access the tables metadata by metalib, or proc sql connect, or even simple proc contents (below), and it's throwing the CLI error:

Example:

PROC SQL;
connect using PGLIB;
select * from connection to PGLIB
      (select 1);
quit;

Log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 PROC SQL;

74 connect using PGLIB;

75 select * from connection to PGLIB

76 (select 1);

ERROR: CLI describe error: E

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

77 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

79 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: SAS9.4 decision manager postgres access through libname

JBailey — Mon, 08 Jul 2019 19:28:45 GMT

Hi @AhmedAccordBGro

Run the following SAS OPTIONS statement and then rerun your code.

OPTIONS SASTRACE="d,,d" SASTRACELOC=saslog NOSTSUFFIX;

Then try adding the following to your LIBNAME statement.

PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES

Here is the full LIBNAME statement (based on the one you posted here):

LIBNAME PBLIB odbc DSN=PGS schema=public PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES;

Best wishes,
Jeff