topic Loading Excel data with special foreign characters into SQL Server via SAS EG in SAS Data Management

Loading Excel data with special foreign characters into SQL Server via SAS EG

SASame — Thu, 29 Nov 2018 19:23:54 GMT

Software Versions: SAS EG 6.1, MS Office 2010 Pro Plus 2010 , SQL Server 2014, Linux 2.6.32-754.6.3.el6.x86_64

Data is received from various countries in Excel. Some text fields contain special foreign characters. Import via the wizard is not used because numeric fields can have bogus text such as 'N/A', which throws off the data types. Worksheets from each country are saved as tab delimited files and copied to Linux folders from where they're concatenated in SAS Enterprise Guide via a FILENAME statement and then input by a data step. In the data step, the INFILE statement has dsd dlm='09'x ENCODING='LATIN9'.

Everything works fine until the data is uploaded to SQL Server.

To upload, first a libname mylib statement signs on to the right datasrc and schema. Then proc sql is used to execute the following:
create table mylib.mytable as select * from mySASdataset;

The problem is that all text fields in SQL Server are uploaded as varchar ... not Nvarchar (i.e. unicode).

1. In this case, does varchar instead of Nvarchar mean that various special foreign characters might not be supported.
2. If so, how can the Nvarchar data type be forced for the fields that may contain special foreign characters?

Re: Loading Excel data with special foreign characters into SQL Server via SAS EG

SuryaKiran — Thu, 29 Nov 2018 19:47:57 GMT

Hi,

When loading into SQL Server only default Data Types are used by SQL Server interface. Check this document for Default SQL Server data types.

You may have to explicitly specify the format.

1) Create a LIBNAME with connection=global and dbmstemp=yes

2) Using pass-through create an empty table in SQL Server with required formats.

3) Now, load the data into the empty table.

You need to setup a single connection to SQL Server that shared by multiple proc steps and data steps (step 1 does that).

This paper might help you some: https://www.lexjansen.com/sesug/2018/SESUG2018_Paper-154_Final_PDF.pdf

Re: Loading Excel data with special foreign characters into SQL Server via SAS EG

SASame — Thu, 29 Nov 2018 20:45:07 GMT

Hi Suryakiran,

FANTASTIC !!! Thank you so much !!!

-Peter