04-05-2016 06:04 AM
I'm having an issue regarding to getting data from views in a database SQL Server 2008 to SAS v9.2, using ODBC (driver SQL Server Native Client 10.0), where some of the fields present in those views that are formatted with SQL Float and being read in SAS as char (more specifically $768.).
In the past this database was in a SQL Server 2000, and it was restored in SQL Server 2008. I've tried different ODBC driver as well (ODBC Driver 11 for SQL Server and SQL Server - 6.00.6002.18005).
Any idea about what is causing this?
Thanks in advance.
04-05-2016 10:41 AM
04-05-2016 12:34 PM
First of all thanks for your reply.
"You are sure that the column in the table/view in SQL Server is defined as float?" This was told to me by an element of another team who's not really the DB administrator, but i've been querying and it seems that is a NVARCHAR. Is this more accurate?
Can in be related to encoding in SAS session and encoding in SQL Server 2008?
Have ran the query:
connect to ODBC(DSN=MYDSN user=USER pwd=PASSWORD);
select * from connection to ODBC
(SELECT collation_name FROM sys.databases
WHERE name = 'MYDSN ');
And the result was: collation_name - SQL_Latin1_General_CP1_CI_AS
In SAS Session we are using WLATIN1 encoding.
04-05-2016 06:48 PM
Here is a list of the SQL Server data types SAS supports:
There is no "NVARCHAR" listed.
Looking at the SQL Server doc NVARCHAR is still a character type so it looks like SAS is translating it correctly:
04-05-2016 04:53 PM
04-13-2016 05:45 AM
After some more research and acess the SQL Server DB i have clarified that the fields are formatted with SQL Float and not NVARCHAR.
The information contained in http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001384032.htm tells that SQL_Float format doesn't have a default SAS format (same info that SASKiwi have sent for SAS v9.4), so i assume that the format for those fields must be changed to numeric or decimals, so SAS can translate correctly.
Already asked this to SAS Support and still waiting for a response, when i get it i'll put it here.
Thank you all.