BookmarkSubscribeRSS Feed
Fluorite | Level 6


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.

Tourmaline | Level 20
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..?
Options sastrace =',,,d' sastraceloc =saslog nostsuffix msglevel =i;
prior to your SAS query and you will hopefully see what is happening behind the scenes.
Data never sleeps
Fluorite | Level 6

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: 
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.


Kind Regards,

António Pereira.

Opal | Level 21

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:



Tourmaline | Level 20
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.
Data never sleeps
Fluorite | Level 6

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 tells that SQL_Float format doesn't have a default SAS format (same info that 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.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3 in conversation