BookmarkSubscribeRSS Feed
ArmyMP
Calcite | Level 5

Using SAS EG with an Oracle connection. Whenever I pull the Name field or Address field from Table A, I get ORA-29275 partial multibyte character error. My understanding is that there are characters within these columns that SAS EG may not recognize because the encoding from Oracle does not align with SAS EG.

 

This happens when applying a simple filter which includes those columns or if I create a join to another table.

 

I've tried using the TRIM function, KTRIM, formatting the fields as UTF8 to no avail.

 

I'm just trying to quickly find a workaround as I'm up against a deadline and I'm hitting a roadblock. I've read the forums and can't seem to find a solution. Any assistance is greatly appreciated.

3 REPLIES 3
Sven111
Pyrite | Level 9

If you're using explicit pass-through sql or can create views and pull from those, try wrapping the troublesome columns in the Oracle ASCIISTR function.  This will replace any non-ASCII chars with the UTF-16 code unit.  

 

This error seems odd though.  Both SAS and Oracle are generally pretty good in my experience at handling multiple encodings. Can you pull the data without error if you omit those columns?

ArmyMP
Calcite | Level 5

@Sven111 wrote:

If you're using explicit pass-through sql or can create views and pull from those, try wrapping the troublesome columns in the Oracle ASCIISTR function.  This will replace any non-ASCII chars with the UTF-16 code unit.  

 

This error seems odd though.  Both SAS and Oracle are generally pretty good in my experience at handling multiple encodings. Can you pull the data without error if you omit those columns?


 

I think I would need to get a DBA involved to do your first suggestion.

 

If I omit those columns from my output then I do not get an error. Unfortunately the data I need is from those columns.

Sven111
Pyrite | Level 9

You may not have the permissions to create views in the schema you're accessing, but I think if your able to connect using implicit SQL pass-through (which you seem to have), then you should be able to connect via explicit SQL pass-through without needing assistance or modification of the DB.  Try something like this:

 

LIBNAME OracSAS ORACLE USER=&UserNm PASS=&PassWd PATH='DB_NAME' CONNECTION=GLOBAL SCHEMA=SCHEMA_NAME; 
PROC SQL;
CONNECT USING OracSAS AS OracDB;

SELECT *
FROM CONNECTION TO OracDB (SELECT ASCIISTR(COLUMN_A) AS COL_A_ASCII
FROM TABLE_1);
DISCONNECT FROM OracDB;
;QUIT;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4151 views
  • 0 likes
  • 2 in conversation