SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Occasional Contributor
Posts: 10

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

[ Edited ]

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.

Super User
Posts: 17,784

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

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

Occasional Contributor
Posts: 10

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

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

Occasional Contributor
Posts: 10

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

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

Occasional Contributor
Posts: 10

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

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

 

Capture.PNG

Super User
Posts: 3,102

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

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.

Occasional Contributor
Posts: 10

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

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

Super User
Posts: 3,102

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

[ Edited ]

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

 

 

Ask a Question
Discussion stats
  • 7 replies
  • 147 views
  • 0 likes
  • 3 in conversation