DATA Step, Macro, Functions and more

SAS error from SQL query of XML data

Reply
New Contributor
Posts: 2

SAS error from SQL query of XML data

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;

 

Valued Guide
Posts: 590

Re: SAS error from SQL query of XML data

[ Edited ]

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; 

 

Thanks,
Suryakiran
New Contributor
Posts: 2

Re: SAS error from SQL query of XML data

Posted in reply to SuryaKiran

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.

Ask a Question
Discussion stats
  • 2 replies
  • 48 views
  • 0 likes
  • 2 in conversation