What proc sql OPTIONS should I use to avoid getting UNICODE Replacement Characters?

cellurl1 — Thu, 29 Dec 2022 01:45:25 GMT

Code below mistakenly converts a unicode: Ø and in the destination oracle sql database it appears as a funny <?>

Details below:

UnicodeCodePoint character UTF-8(hex.) name

U+00D8 Ø c3 98 LATIN CAPITAL LETTER O WITH STROKE

and turns it into a

U+FFFD <?> Replacement Character

---------

Is there an option to prevent that.

Any leads appreciated!

Thanks for looking!

-Jim

87 proc sql;
88 insert into oralib.pax_customer2( bulkload=yes bl_direct_path=yes bl_delete_files=no
89 bl_datafile="/var/xxxxx/rmm/sprcg2/app3536847/sas/ish_extracts/prog/bl_customer_datafile.dat"
89 ! BL_OPTIONS='SILENT=FEEDBACK',
90 cust_nbr, cust_nm, cust_cur_stat_cd, ctry_cd, inln_cd,cust_type_cd,grnd_cust_nbr
91 )
92 (
93 select
94 cust_nbr,
95 cust_nm,
96 cust_cur_stat_cd,
97 ctry_cd,
98 inln_cd,
99 cust_type_cd,
^L3 The SAS System 19:08 Wednesday, December 21, 2022

100 case when grnd_cust_nbr > 0 then grnd_cust_nbr else 0 end as grnd_cust_nbr
101 from
102 here.customer
103 where
104 cust_nbr > 0
105 );
WARNING: Character expression will be truncated when assigned to character column CUST_CUR_STAT_CD.
NOTE: 32931679 rows were inserted into ORALIB.PAX_CUSTOMER2.

************** Begin: SQL*Loader Log File **************

SQL*Loader: Release 19.0.0.0.0 - Production on Wed Dec 21 19:09:28 2022
Version 19.4.0.0.0

Control File: /var/xxxxx/SAS_util00010000E70D_prh90125.rsa.sbe.xxxx.com/BL_PAX_CUSTOMER2_0.ctl
Data File: /var/xxxxx/rmm/sprcg2/appxxxxx/sas/ish_extracts/prog/bl_customer_datafile.dat
File processing option string: "FIX 89 "
Bad File: /var/xxxxx/saswork/SAS_util00010000E70D_prh90125.rsa.sbe.xxxxx.com/BL_PAX_CUSTOMER2_0.bad
Discard File: /var/xxxxx/saswork/SAS_util00010000E70D_prh90125.rsa.sbe.xxxxx.com/BL_PAX_CUSTOMER2_0.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0

Re: What proc sql OPTIONS should I use to avoid getting UNICODE Replacement Characters?

Patrick — Thu, 29 Dec 2022 02:03:11 GMT

It's likely the value of NLS_LANG that needs change - which is something a SAS Admin would need to do.
https://support.sas.com/kb/18/688.html

You can determine what currently set in SAS and Oracle using the commands from the following SAS Note
https://support.sas.com/kb/51/411.html

Even though above two notes are for reading from Oracle the same will also apply for writing to Oracle.

topic What proc sql OPTIONS should I use to avoid getting UNICODE Replacement Characters? in SAS Programming

What proc sql OPTIONS should I use to avoid getting UNICODE Replacement Characters?

Re: What proc sql OPTIONS should I use to avoid getting UNICODE Replacement Characters?