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: 18

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

Posted in reply to anandsasbi

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: 10,044

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

Posted in reply to anandsasbi

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: 10,044

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

Posted in reply to anandsasbi

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
  • 129 views
  • 0 likes
  • 3 in conversation