a week ago
I am trying to use a SQL query in SAS to pull some data that is in a SAS library. However, there is an field with XML data that keeps giving me an error if I try to extract the data for a particular month. I assume it's a character that isn't recognized, but I can't actually see the data. All of the other months that I have tried to extract work fine, and my query works if I remove the XML field.
Does anyone know how I might be able to adjust the code to fix this? I am using SAS 9.4 Classic.
The error that I get is:
ERROR: CLI cursor extended fetch error: [IBM][CLI Driver][DB2/AIX64] SQL20412N Serialization of an XML value resulted
in characters that could not be represented in the target encoding. SQLSTATE=2200W
The query that I've been running is (Error is in the field USER_EVENT_REMARKS):
CREATE TABLE psi_privacy AS
LEFT JOIN psiprod.lab_test_review_result as J ON Event.accessed_entity_sys_id=J.lab_test_review_result_sys_id
FULL JOIN psiprod.lab_report as R ON R.lab_report_sys_id = J.lab_report_sys_id
FULL JOIN psiprod.lab_rpt_client as C ON C.lab_report_sys_id = J.lab_report_sys_id
a week ago - last edited a week ago
It might be because of encoding issue. You might need to use UTF-8 encoding instead of LATIN1. What SAS IDE are you using?
If you have PC SAS installed then you can might have SAS utf-8 (Start/all programs /sas/sas utf-8), try running there or check this.
Check your SAS Session encoding:
proc options option=encoding; run;
a week ago
Thanks for your response. It is utf-8:
183 proc options option=encoding;
SAS (r) Proprietary Software Release 9.4 TS1M3
ENCODING=UTF-8 Specifies the default character-set encoding for the SAS session.
That file from the link you sent is -config "C:\Program Files\SASHome\SASFoundation\9.4\nls\en\sasv9.cfg" rather than the U8. Would I need to change it to U8?
Alternatively, do you know if there is a way to change the encoding through a SAS code instead?
Thank you for your help.