Some characters might not render correctly when data is read from the Microsoft SQL Server database to a SAS Unicode session


Microsoft SQL Server supplies six different data types that can hold textual data: CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, and NTEXT.  Types NCHAR, NVARCHAR, and NTEXT are used to store Unicode data; types CHAR, VARCHAR, and TEXT are used to store non-Unicode character data.

If you launch a SAS Unicode session to process multilingual data from Microsoft SQL Server through ODBC, you should use either the NCHAR, NVARCHAR, or NTEXT types to process data.  If you want to access data of the either the CHAR, VARCHAR, or TEXT data types, you need to make sure that the SAS session encoding matches the operating system's locale.

However, a SAS Unicode session might not render correctly any non-Unicode character data from Microsoft SQL Server.  If the operating system's locale and the database locale are the same, the ODBC driver does not transcode the data, and SAS cannot determine the encoding.

To work around the issue, correct the encoding attribute of the SAS data set that is created from the database to convert it to session encoding, as shown in this example:

libname mydblib odbc dsn=sqlservr uid=testuser pwd=testpass;
proc sql;
create table work.employees as
select name from mydblib.employees ;
quit;
/* Correct the encoding attribute. */
proc datasets library=work;
modify employees/correctencoding=wlatin1;
quit;
data work.employees2;
set work.employees;
run;