Issue of transcoding in utf-8

[jacques]

I have a strange issue here between 2 workstations with exactly the same SAS version (9.4), the same encoding (utf-8), the same LOCALE (EN_US), the same OS (win10) and the same postgres ODBC Version:

when I run this script below, it works for one machine but not for the other:

%let _studypath=L:\sas_view\Rave\Imp\prod;


Libname ext "&_studypath\data\external" ;

PROC SQL;
connect to odbc as odbc(datasrc="VMBRPGPROD");

create view ext.v_site_list as select
SITE label="site" format $30.
,INV_LASTNAME label="Investigator lastname" format $30. length=30
,INV_FIRSTNAME label="Investigator firstname" format $30. length=30
,EMAIL label="Email" format $50.
,ADDRESS label="Address" format $255.
,CITY label="City" format $30.
,POSTAL_CODE label="Postal_code" format $10.
,COUNTRY label="Country" format $30.
,PHONE label="Phone" format $30.
from connection to odbc (
select
center_number as site
,convert_to(lastname,'UTF8') as inv_lastname
,convert_to(firstname,'UTF8') as inv_firstname
,convert_to(email,'UTF8') as email
,convert_to(address,'UTF8') as address
,convert_to(city,'UTF8') as city
,convert_to(postal_code,'UTF8') as postal_code
,country as country
,convert_to(phone,'UTF8') as phone
from contacts.v_imp_sites_pi);


disconnect from odbc ;

quit ;

proc contents data=ext.v_site_list; run;
quit ;

The CONTENTS Procedure
Data Set Name EXT.V_SITE_LIST Observations .
Member Type VIEW Variables 9
Engine SQLVIEW Indexes 0
Created 11/14/2022 12:45:30 Observation Length 1395
Last Modified 11/14/2022 12:45:30 Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation Default
Encoding Default
Alphabetic List of Variables and Attributes
# Variable Type Len Flags Format Informat Label
5 ADDRESS Char 255 P-- $255. $HEX510. Address
6 CITY Char 255 P-- $30. $HEX510. City
8 COUNTRY Char 30 P-- $30. $30. Country
4 EMAIL Char 255 P-- $50. $HEX510. Email
3 INV_FIRSTNAME Char 30 P-- $30. $HEX510. Investigator firstname
2 INV_LASTNAME Char 30 P-- $30. $HEX510. Investigator lastname
9 PHONE Char 255 P-- $30. $HEX510. Phone
7 POSTAL_CODE Char 255 P-- $10. $HEX510. Postal_code
1 SITE Char 30 P-- $30. $30. site

On the other computer, I have this result when I run the proc content of the same view:

Alphabetic List of Variables and Attributes
# Variable Type Len Flags Format Informat Label
5 ADDRESS Char 1 P-- $255. $HEX2. Address
6 CITY Char 1 P-- $30. $HEX2. City
8 COUNTRY Char 30 P-- $30. $30. Country
4 EMAIL Char 1 P-- $50. $HEX2. Email
3 INV_FIRSTNAME Char 30 P-- $30. $HEX2. Investigator firstname
2 INV_LASTNAME Char 30 P-- $30. $HEX2. Investigator lastname
9 PHONE Char 1 P-- $30. $HEX2. Phone
7 POSTAL_CODE Char 1 P-- $10. $HEX2. Postal_code
1 SITE Char 30 P-- $30. $30. site

And when I run the script for creating the view, I have a failure of transcoding from utf-8 to utf-16le.

I don't understand why I don't have the same behavior and the same reading of the same view between the 2 workstations.

However, when I run the script with LATIN1 for encoding, it works on the second machine.

[Tom]

Did you check that the definition of the ODBC connection you are calling 

VMBRPGPROD

is this same on both machines? 

[jacques]

yes, they are exactly the same: same driver version (PostgreSQL Unicode(x64) 13.02)