topic Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type in SAS Data Management

Date imported as character in SAS from MSSQL table instead of default SAS numeric type

Sulthan — Wed, 01 Feb 2017 02:53:08 GMT

Dear All,

Recently we have installed SAS ACCESS LICENSE to MSSQL in our production environtment , connection established sucessfull, and while importing table from DB we are receiving DATE datatype as character instead of NUMERIC.

Please suggest why its happening.

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

Reeza — Wed, 01 Feb 2017 02:53:38 GMT

What's the type/format in MS SQL? Have you tried over riding it using DBSASTYPE option?

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

SASKiwi — Wed, 01 Feb 2017 03:30:00 GMT

We struck a similar problem with SQL Server and it was solved by changing to a more recent ODBC driver. Check to see if SAS/ACCESS to SQL Server is using ODBC behind the scenes, and if so which driver.

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

Sulthan — Wed, 01 Feb 2017 03:43:57 GMT

HI Reeza,

IN MSSQL the type format is DATE, We have faced the same for DATETIME2 formats imported as character but upon changing DATETIME2 to DATETIME we could import correctly to NUMERIC but for DATE we couldnt define anyother datatype apart from DATE

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

Sulthan — Wed, 01 Feb 2017 03:48:30 GMT

Hi User,

Thanks for the quick response on my query,

Yes we are using ODBC behind the scene we have our driver installed on the below path,

/usr/SAS/ODBC

I dont know how to check for the latest version on my ODBC

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

SASKiwi — Wed, 01 Feb 2017 23:28:06 GMT

We are using SQL ODBC driver Server Native Client 11.0. Prior to that we were using ODBC driver SQL Server 6.03. Maybe your Unix admin can help you here. We use Windows so I can't check where you would look for this on Unix.

However just try a Google search for the appropriate ODBC driver for your OS. I found this link which includes Linux:

https://msdn.microsoft.com/en-us/library/mt703139(v=sql.1).aspx

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

Sulthan — Wed, 01 Feb 2017 06:45:47 GMT

We havent used those steps , and aware not where to mention them whether while assigning the libname ? or how

Re: Date imported as character in SAS from MSSQL table instead of default SAS numeric type

Sulthan — Wed, 01 Feb 2017 07:14:19 GMT

I have mentioned the DBSASTYPE while importing via DI ,but still i am seeing date as character