How to fix encoding issue due to special characters using SUBCHAR

[lhpm2024]

I was trying to create a table that may contain some special characters, which keeps resulting in the error:

ERROR: Open error: Failed to transcode result set data in column 'X', row 296, to SESSION encoding. Use the SUBCHAR= LIBNAME/DATA SET option to resolve transcoding issues.

Following are the codes I used to create the table. I have tried to add (SUBCHAR=UESC) to the create table line but it's not working. I'm sure that the issue is coming from the column containing some special characters or punctuations, but have no idea how to do the configuration on SAS. I don't mind simply ignoring those special characters, but right now the whole table cannot be created because of that error. Any help is appreciated!

Proc SQL;

CONNECT TO HADOOP(SERVER="...");

create table work.Temp as

SELECT * FROM CONNECTION TO HADOOP ( these are the simple select * from * SQL codes that can be ignored ) ;

disconnect from Hadoop;

quit;

[SASJedi]

I suspect that your SAS session encoding does not match the encoding of your Hadoop connection. By default, Hadoop uses UTF-8 encoding. You can check the encoding of your SAS session using PROC OPTIONS:

proc options option=encoding; 
run;

If your SAS session is not UTF-8 and you are running PC SAS, go to the start menu, find the SAS group, and look for "SAS 9.4 (Unicode Support)". Try running your code in that configuration. If you are not running a locally installed SAS, you may not have the ability to change the encoding. In that case, consider using a LIBNAME statement to connect to Hadoop with the SUB_CHAR= option, and then using the CONNECT USING statement in your SQL. Example pseudo-code provided here:

libname hdp hadoop user='my-user-name' pwd='my-password' server='hive.server.com'
		  sub_char=QUESTIONMARK;

proc SQL;
connect using hdp;
create table work.Temp as
	SELECT * 
		FROM CONNECTION TO hdp 
			(SQL Passthrough code)
;
disconnect from hdp;
quit;

May the SAS be with you!
Mark

[kazakzhao]

I have similar code and get same error message. ERROR: Open error: Failed to transcode result set data in column 'ID'.

Based on the above Jedi's hint, I add sub_char=QUESTIONMARK after your server option (see below). It works for me. You may try it.

/******************************************************************************************************************/

Proc SQL;

CONNECT TO HADOOP(SERVER="..."   sub_char=QUESTIONMARK
);

create table work.Temp as

SELECT * FROM CONNECTION TO HADOOP ( these are the simple select * from * SQL codes that can be ignored ) ;

disconnect from Hadoop;

quit;

/*****************************************************************************************************************/

the code goes through and only receives warning message in the log:

WARNING: At least one column failed to transcode. Characters that could not be transcoded were replaced with the SUBCHAR option
value. Details of which columns failed can be seen with sastrace=',,d' enabled.