Retrieving data from SQL Server 2008 - Float format read as char

[Antonio84Pereira]

Hello,

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.

António Pereira.

[LinusH]

So, the problem is that float in SQL Server is interpreted as char in SAS?
You are sure that the column in the table/view in SQL Server is defined as float? And this worked when it was in SQL Server 2000..?
Issue:
Options sastrace =',,,d' sastraceloc =saslog nostsuffix msglevel =i;
prior to your SAS query and you will hopefully see what is happening behind the scenes.

[Antonio84Pereira]

Hi there,

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: 
PROC SQL;
connect to ODBC(DSN=MYDSN user=USER pwd=PASSWORD);
select * from connection to ODBC
(SELECT collation_name FROM sys.databases
WHERE name = 'MYDSN ');
QUIT;

And the result was: collation_name - SQL_Latin1_General_CP1_CI_AS

In SAS Session we are using WLATIN1 encoding.

Kind Regards,

António Pereira.

[SASKiwi]

Here is a list of the SQL Server data types SAS supports:

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n0h4i25zq3t58en1lg...

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:

https://msdn.microsoft.com/en-nz/library/ms186939.aspx

[LinusH]

So if I understand you correctly, there is no issue. The column is defined as NVARCHAR in SQL Server and CHAR in SAS. Normal data type mapping.
If you would like it to be FLOAT and NUMERICAL in SAS you need either to change the data type in SQLS (in a view if the DBA wishes it to change the table definition) or in SAS using the input() function.

[Antonio84Pereira]

Good morning,
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.

Kind regards.
António Pereira.