BookmarkSubscribeRSS Feed
lhpm2024
Calcite | Level 5

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;

1 REPLY 1
SASJedi
SAS Super FREQ

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

Check out my Jedi SAS Tricks for SAS Users

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1187 views
  • 1 like
  • 2 in conversation