topic SAS error from SQL query of XML data in SAS Programming

SAS error from SQL query of XML data

CSmo — Tue, 10 Jul 2018 18:32:07 GMT

Hello,

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

proc sql;

CREATE TABLE psi_privacy AS

(SELECT Event.USER_EVENT_SYS_ID,

Event.USER_EVENT_USER_ID,

Event.USER_EVENT_TMSTMP,

Event.USER_EVENT_CODE,

Event.USER_EVENT_FUNC_CNTXT_CODE,

Event.USER_EVENT_ACCESS_TYPE_CODE,

Event.USER_EVENT_CURRENT_ROLES,

Event.USER_EVENT_IP_ADDRESS,

Event.ORG_FAC_ID,

Event.USER_EVENT_REMARKS,

J.lab_test_review_result_sys_id,

R.PREFERRED_IDENTIFIER,

R.PREFERRED_NAME_LAST,

R.PREFERRED_NAME_FIRST,

C.BIRTH_DATE

FROM

psiprod.User_Event Event

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

WHERE month((Event.USER_EVENT_TMSTMP))=&month.);

quit;

Re: SAS error from SQL query of XML data

SuryaKiran — Tue, 10 Jul 2018 18:53:19 GMT

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;

Re: SAS error from SQL query of XML data

CSmo — Tue, 10 Jul 2018 19:10:51 GMT

Thanks for your response. It is utf-8:

183 proc options option=encoding;

184 run;

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.