topic Re: ODBC to Postgres DB is translating UTF-8 data into Latin1? in SAS Programming

ODBC to Postgres DB is translating UTF-8 data into Latin1?

pchilst — Sat, 12 Jun 2021 15:20:19 GMT

Hi all,

We've tried many solutions here but are encountering a problem still. I am opening an ODBC to a Postgres DB like this:

libname sql_read odbc dsn=OC64 schema=my_schema;

And it works, but the problem is the database is encoded with UTF-8, and some columns with unicode characters get translated over to SAS as non-unicode characters. For example,

≥

gets turned into "=".

We've tested the exact same driver and ODBC in Python, and this problem doesn't exist, i.e. it is not mis-translating the data. So that suggests the problem is not with the driver or ODBC.

Some other things we've tried are:

1. Ensuring the SAS session encoding is UTF-8. (Verifying this by doing a datalines with UTF-8 characters, which are correctly read)

2. Ensuring the column read into SAS has enough characters (it is character type and length 1024)

3. Trying to use the "correctencoding" argument, e.g.

proc datasets library=perm nodetails nolist;
modify my_table/ correctencoding='utf8';
quit;

4. Trying to use the "outencoding" argument on the library, e.g.

libname perm "my_path" outencoding='UTF-8';

5. Trying to use a PROC SQL odbc passthrough instead of the libname statement (same result).

Thank you for any help.

Re: ODBC to Postgres DB is translating UTF-8 data into Latin1?

SASKiwi — Sun, 13 Jun 2021 00:13:33 GMT

What is your SAS session encoding?

proc options option = encoding ;
run;

Re: ODBC to Postgres DB is translating UTF-8 data into Latin1?

pchilst — Sun, 13 Jun 2021 00:16:08 GMT

It's UTF-8 according to that proc. I am opening SAS by pointing it to the UTF8 config file:

"C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.4\nls\u8\sasv9.cfg"

Thanks.

Re: ODBC to Postgres DB is translating UTF-8 data into Latin1?

SASKiwi — Sun, 13 Jun 2021 00:36:13 GMT

Hmm, usually encoding problems are solved by aligning encoding settings between applications. It looks like there are some character set differences between Postgres's UTF-8 and SAS's. I'd suggest raising a Tech Support track about this as they are in a better position to help.

Re: ODBC to Postgres DB is translating UTF-8 data into Latin1?

pchilst — Sun, 13 Jun 2021 14:18:42 GMT

Sounds good, thanks for clarifying

Re: ODBC to Postgres DB is translating UTF-8 data into Latin1?

waveson — Fri, 18 Mar 2022 12:44:18 GMT

For anyone looking for solutions to this...
I just had the same problem - UTF8 sas session, UTF8 postgres db, but still getting ANSII characters.
I have solved it by adding connect settings in ODBC sources:

SET CLIENT_ENCODING TO 'UTF8'