topic Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format in SAS Programming

Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format

anandsasbi — Wed, 19 Jul 2017 05:04:16 GMT

Hi All,

I have Chinese language data in source file (.xlsx) . I am able to import it in proper Chinese language in SAS table (through UTF8 sas server) .

But when I am trying to insert SAS table data into target table ( oracle table ) than all Chinese language data converted into ‘?’ marks .

When Target team trying to load some sample Chinese language data directly on oracle table using import option than it’ coming in proper Chinese language format .

In same table some column have data in number , date and English language data and those data inserted in proper format .

I am using insert statement for load data into Oracle table.

libname SAS_DEV oracle authdomain=’XXXXXXXXX’ path=”XXXXXXXXX’ schema='SAS_DEV';

proc sql;

Insert into SAS_DEV.FX_MARKET_UPDATE_EXTERNAL

(

ENG_DESC,SIM_CHIN_HEADER_DESC ,SIM_CHIN_HEADER_DESC, AMT_NUM, CREATE_DT

)

select ENG_DESC,SIM_CHIN_HEADER_DESC ,SIM_CHIN_HEADER_DESC, AMT_NUM, CREATE_DT

from WORK.FX_MARKET_SIM_CHINESE;

quit;

Is anyone help me in this issue?

Please see attached file

Re: Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format

EdwardJin — Wed, 19 Jul 2017 05:28:19 GMT

Hi there,

You may need to check the character-set encoding for your SAS environment.

Can you run the following lines and paste the output, especially for ENCODING and LOCALE:

proc options group=languagecontrol;
run;

BR./Edward

Re: Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format

Ksharp — Wed, 19 Jul 2017 13:48:42 GMT

Make sure SAS and Oracle have the same encoding.

Change environment variable in sasenv_local .

EXPORT xxxxx=xxxxxxx

Re: Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format

anandsasbi — Thu, 20 Jul 2017 03:22:52 GMT

I have used below line before insert SAS table data in Oracle but it’s not working result is same .

proc options group=language control;

run;

when I am running code under UTF-8 SAS Session than SAS table showing data in Chinese language but when same SAS table I use to open in SAS Normal session than Chinese data not showing in proper order.

It’s means SAS internally storing data in “?” or “|_” symbol. Only showing in proper format in UTF-8 SAS Session. My SAS session support UTF-8 .

@Ksharp :- could you please explain me how to change environment variable in sasenv_local when I am running my SAS code. So data store in Multi byte under SAS table.

If you have any example code than please let me know.

Re: Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format

Ksharp — Thu, 20 Jul 2017 12:45:48 GMT

Sorry. Based on my experience before. If your SAS is under AIX / UNIX , there is a file named sasenv_local you can use.

Change the encoding of SAS into the same as Oracel. I have forgotten how to edit it.

Search 'sasenv_local ' at support.sas.com .

File to modify:
/app/sas/sashome/SASFoundation/9.4/bin/sasenv_local
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Re: Unable to load Chinese language data (UNICODE) from SAS table to Oracle table in proper format

anandsasbi — Fri, 21 Jul 2017 07:34:09 GMT

I have already used (x 'export NLS_LANG=UTF-8';) in my code before LIB statement .

x 'export NLS_LANG=UTF-8';
libname SAS_DEV oracle authdomain='*************'path='***********'schema='SAS_DEV';
data SAS_DEV.FX_MARKET_SIM_CHINESE
length CHINESE Nvarchar(40) ;
set work.Test2 ;
run;

but same '?' showing in Oracle table. 😞