DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 35

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

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

 

Occasional Contributor
Posts: 17

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

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

Super User
Posts: 9,662

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

Make sure SAS and Oracle have the same encoding.

Change environment variable in sasenv_local .

EXPORT xxxxx=xxxxxxx

Contributor
Posts: 35

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

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.

Super User
Posts: 9,662

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

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
Contributor
Posts: 35

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

I have already used (x 'export NLS_LANG=UTF-8'Smiley Wink 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. Smiley Sad

Ask a Question
Discussion stats
  • 5 replies
  • 111 views
  • 0 likes
  • 3 in conversation