topic How do I read SQL special characters correctly in SAS EG in SAS Programming

How do I read SQL special characters correctly in SAS EG

schatja — Wed, 09 Jun 2021 02:57:37 GMT

Calling on my SAS community. I need a little help. We have some data that is not playing nice. This data contains about 6 fields with various types of comments. It's stored in SQL and we use Enterprise Guide metadata to make our Oracle connections. The problem is that depending on how the data was entered, it can cause special characters that read fine in SQL, to only display as "?" in SAS. I can do a series of If Then statements or Case statements to fix this but I know that's only a bandage. I'm sure there's some conversion or something I could to make it so. I've tried encoding options or KCVT function. It doesn't seem to help. Any other options???

Re: How do I read SQL special characters correctly in SAS EG

jimbarbour — Wed, 09 Jun 2021 04:04:18 GMT

Interesting. Do you know the exact hexidecimal values for those "?" marks? Perhaps you could grab a few examples, isolate the word that has the "?", and then use the $HEX format to write the actual hexidecimal value to the SAS log. I suspect what may be happening is that these may be "smart" quotes or some such that are not part of the base ASCII character set. Really only values 00 through 7F have standard definitions. For the extended range, 80 - FF, different machines do different things. I suspect the server is doing it one way and SAS is doing it another, at least that's what I've seen in the past.

Why don't you determine the exact ASCII values (hexidecimal values in other words), and then we can go from there? We can do some kind of Translate function or some such to convert from the values the server is using to something that works in SAS.

Jim

P.S. In addition to the above, could you run the following code and post the results? Also, could you ask whomsoever administers the database what encoding they are using? The answers to these may give us a more elegant way of resolving this issue. I see that you've played with the encoding already, so we may need to use TRANSLATE, but still I'm curious as to what's really going on, and perhaps something will yet occur to us that has not yet.

PROC	OPTIONS	OPTION=ENCODING;
RUN;

PROC	OPTIONS	OPTION=LOCALE;
RUN;

Re: How do I read SQL special characters correctly in SAS EG

andreas_lds — Wed, 09 Jun 2021 05:00:47 GMT

Things to check:

encoding of the sas-session
encoding used in oracle
encoding-setting in the connection from sas to oracle

Most likely oracle uses utf-8, but sas uses some old-school ascii-encoding. Unfortunately you can't just change the encoding of the sas-session, the admins have to setup an app-server using unicode / utf-8.

Re: How do I read SQL special characters correctly in SAS EG

Sajid01 — Wed, 09 Jun 2021 12:28:09 GMT

Hello @schatja
Please have a look at this https://blogs.sas.com/content/sgf/2017/05/19/demystifying-and-resolving-common-transcoding-problems/
Multiple strategies have been discussed. Please look at the Suppress transcoding section. That may help resolve some of the issues you have.