63170 - "Unable to transcode data to/from UCS-2 encoding" occurs after invalid characters are read in an Amazon Redshift, Impala, Netezza, or PostgreSQL table

Usage Note 63170: "Unable to transcode data to/from UCS-2 encoding" occurs after invalid characters are read in an Amazon Redshift, Impala, Netezza, or PostgreSQL table

When a character string in an Amazon Redshift, Impala, Netezza, or PostgreSQL table contains a character that cannot be represented in the SAS^® session encoding, you receive the following error:

ERROR "Unable to transcode data to/from UCS-2 encoding"

The default SAS session encoding is UTF-8. Warnings are generated in the SAS log under these circumstances:

Your data must be transcoded from another encoding to the SAS session encoding.
Your data contains characters that cannot be transcoded to the destination encoding.

For details about rows and columns that are affected by transcoding errors, enable the SASTRACE= option:

options sastrace=',,d' sastraceloc=saslog nostsuffix;

Workaround

Change the encoding for your environment to UTF-8. If an encoding migration is not feasible, see the following section.

New SUB_CHAR= option

SAS^® 9.4M6 offers support for a new SUB_CHAR= LIBNAME statement option for SAS/ACCESS^® Interface to Amazon Redshift, SAS/ACCESS^® Interface to Netezza, and SAS/ACCESS^® Interface to PostgreSQL. This option specifies a substitution character to use in place of any invalid characters that cannot be represented in the SAS session encoding. This option will be available for SAS/ACCESS^® to Impala with SAS^® 9.4M7

This new option is also available in a forthcoming hot fix for other maintenance releases of SAS^® 9.4.

Details about the syntax follow here:

SUB_CHAR=value

Syntax Description

value

specifies the substitution character to use in place of a character that cannot be represented in the SAS session encoding.

Here are the possible values for SUB_CHAR=:

SPACE

specifies that a space replaces an invalid character during transcoding.

SUB

specifies that the default substitution character for the source encoding replaces an invalid character during transcoding. The default substitution character varies depending on your source encoding and on your system platform. For example, the substitution character might be represented by a “0x1A” character in a Windows environment and by a “?” in a Linux environment.

UESC

specifies that the “\uddd” value replaces an invalid character during transcoding. This UESC value is typically used only during debugging and is not intended for large-scale jobs. (Note: In SAS^® 9.4M4F, the UESC option is not available because it is not in the underlying routines for national language support.)

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

Product Family	Product	System	SAS Release
Product Family	Product	System	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Amazon Redshift	HP-UX IPF	9.4 TS1M4	9.4 TS1M6
		64-bit Enabled Solaris	9.4 TS1M4	9.4 TS1M6
		64-bit Enabled AIX	9.4 TS1M4	9.4 TS1M6
		Windows 7 Ultimate x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Ultimate 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Professional x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Professional 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Home Premium x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Home Premium 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Enterprise 32 bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 Std	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Std	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Datacenter	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 Datacenter	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 10	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Pro x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Pro 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Pro x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Pro 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft® Windows® for x64	9.4 TS1M4	9.4 TS1M6
		Linux for x64	9.4 TS1M4	9.4 TS1M6
		Solaris for x64	9.4 TS1M4	9.4 TS1M6
SAS System	SAS/ACCESS Interface to Netezza	Microsoft® Windows® for x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Pro 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Pro x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Pro 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Pro x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 10	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 Datacenter	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Datacenter	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Std	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 Std	9.4 TS1M4	9.4 TS1M6
		Windows 7 Enterprise 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Home Premium 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Home Premium x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Professional 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Professional x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Ultimate 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Ultimate x64	9.4 TS1M4	9.4 TS1M6
		64-bit Enabled AIX	9.4 TS1M4	9.4 TS1M6
		64-bit Enabled Solaris	9.4 TS1M4	9.4 TS1M6
		HP-UX IPF	9.4 TS1M4	9.4 TS1M6
		Linux for x64	9.4 TS1M4	9.4 TS1M6
SAS System	SAS/ACCESS Interface to PostgreSQL	Microsoft® Windows® for x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Pro 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8 Pro x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Pro 32-bit	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 8.1 Pro x64	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows 10	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 Datacenter	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Datacenter	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Std	9.4 TS1M4	9.4 TS1M6
		Microsoft Windows Server 2012 Std	9.4 TS1M4	9.4 TS1M6
		Windows 7 Enterprise 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Enterprise x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Home Premium 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Home Premium x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Professional 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Professional x64	9.4 TS1M4	9.4 TS1M6
		Windows 7 Ultimate 32 bit	9.4 TS1M4	9.4 TS1M6
		Windows 7 Ultimate x64	9.4 TS1M4	9.4 TS1M6
		64-bit Enabled AIX	9.4 TS1M4	9.4 TS1M6
		64-bit Enabled Solaris	9.4 TS1M4	9.4 TS1M6
		HP-UX IPF	9.4 TS1M4	9.4 TS1M6
		Linux for x64	9.4 TS1M4	9.4 TS1M6
		Solaris for x64	9.4 TS1M4	9.4 TS1M6
SAS System	SAS/ACCESS Interface to Impala	Microsoft® Windows® for x64	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8 Enterprise x64	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8 Pro 32-bit	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8 Pro x64	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8.1 Enterprise 32-bit	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8.1 Enterprise x64	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8.1 Pro 32-bit	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 8.1 Pro x64	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows 10	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows Server 2012 Datacenter	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows Server 2012 R2 Datacenter	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows Server 2012 R2 Std	9.4 TS1M4	9.4 TS1M7
		Microsoft Windows Server 2012 Std	9.4 TS1M4	9.4 TS1M7
		Windows 7 Enterprise 32 bit	9.4 TS1M4	9.4 TS1M7
		Windows 7 Enterprise x64	9.4 TS1M4	9.4 TS1M7
		Windows 7 Home Premium 32 bit	9.4 TS1M4	9.4 TS1M7
		Windows 7 Home Premium x64	9.4 TS1M4	9.4 TS1M7
		Windows 7 Professional 32 bit	9.4 TS1M4	9.4 TS1M7
		Windows 7 Professional x64	9.4 TS1M4	9.4 TS1M7
		Windows 7 Ultimate 32 bit	9.4 TS1M4	9.4 TS1M7
		Windows 7 Ultimate x64	9.4 TS1M4	9.4 TS1M7
		64-bit Enabled AIX	9.4 TS1M4	9.4 TS1M7
		Linux for x64	9.4 TS1M4	9.4 TS1M7

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

Date Modified:	2019-01-31 09:11:04
Date Created:	2018-11-05 10:49:24

Type:	Usage Note
Priority:

Support

Usage Note 63170: "Unable to transcode data to/from UCS-2 encoding" occurs after invalid characters are read in an Amazon Redshift, Impala, Netezza, or PostgreSQL table

Workaround

New SUB_CHAR= option

Operating System and Release Information