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

[Sulthan]

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.

[Reeza]

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

[Sulthan]

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

[Sulthan]

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

[Sulthan]

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

[SASKiwi]

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.

[Sulthan]

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

[SASKiwi]

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