ODBC to sas characters getting substituted

[NN]

Hi,

I have a process which pulls in data from an Nvarchar column of a Sql Server into a sas session.

The problem is that some characters in the column are getting replaced with a substitute character like a right arrow -->   

I noticed that some of these characters which are getting replaced have unicode values like 8212 (i.e. dash) and 8217 (i.e. quote)

If some one has faced a similar issue and knows of a solution then please do guide.

The SQL server has a SQL_Latin1 collation,

The SAS Server is on Latin1 session (and sadly cannot be changed to UTF-8)

I need to further push this data to a UTF-8 database , So if there is any solution in which sas can just act as a pull and push mediator without any transcoding that would help.

[LeonidBatkhan]

Hi NN,

You can create your SAS data table in UTF-8 even though you are running LATIN1 SAS session.

Try defining your output SAS library as

libname outlib 'c:\some_folder' outencoding='UTF-8';

For a test, I ran the following code where I defined outlib library that way in my WLATIN1 SAS session and got copied dataset UTF-encoded:

libname outlib 'c:\u8' outencoding='UTF-8';
proc copy in=sashelp out=outlib noclone;
   select cars;
run;

See SAS documentation on the OUTENCODING option in the LIBNAME statement.

Hope this helps.

[NN]

Thanks for the reply Lenoid ,

But my data source is ODBC / SQL server. hence the outencoding / innencoding would not work with an ODBC libname statement.

[LeonidBatkhan]

NN,

I understand that you read from ODBC into SAS, so I suggested using

libname outlib 'c:\some_folder' outencoding='UTF-8';

for your SAS library, not for ODBC library.

[NN]

Hi Lenoid

i did try this

libname sql odbc dsn="sqldsn" AUTHDOMAIN=sql_auth Qualifier=dbname schema=schemaname;
libname sas "/AIX_SAS_FOLDER/" outencoding='UTF-8' ;

data sas.sasdataset;
set sql.sqltable;
keep Column1;
run;

libname sql clear;
libname sas clear;

The log shows that CEDA is still active

NOTE: Data file SAS.SASDATASET.DATA is in a format that is native to another host, or the file encoding does not match the session 
      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: There were 1924 observations read from the data set SQL.sqltable.
NOTE: The data set SAS.SASDATASET has 1924 observations and 1 variables.
NOTE: Compressing data set SAS.SASDATASET decreased size by 87.10 percent. 
      Compressed is 4 pages; un-compressed would require 31 pages.
NOTE: DATA statement used (Total process time):
      real time           0.94 seconds
      cpu time            0.24 seconds

[maggiem_sas]

I believe the log message is telling you that sas.sasdataset doesn't match the session encoding. This is the case because your session encoding is latin1 and you have created a data set that has an encoding of utf-8. 

[NN]

Yes i agree.
So i think its just not possible to download Unicode data from SQL server , in a SAS latin1 session without transcoding.
Hence using a SAS Unicode session seems as the only possible solution. Thank you all for your time.

[ChrisNZ]

I am confused. 

How can the SQL server use a Latin1 collation and store and send UTF-8 characters? 

Is the ODBC driver misconfigured?