SAS data into Ms SQL

[Citrine10]

Hi there

I am writing SAS dataset into an Ms SQL table. I have 2 SAS columns that have type best17.15 in SAS and type numeric(15) in Ms SQL. When it inserts the data these 2 columns populate as NULL in Ms SQL.

Why this does happen and how can I resolve the issue?

[Sajid01]

As you are using best17.15 in SAS, use  numeric(17,15) format in MS SQL

[Citrine10]

Hi Sajid, that give me an error.
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic overflow error converting
float to data type numeric. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The statement has been
terminated.

[Sajid01]

No worries. You are now near the solution.

You have two options

Option 1

Use FLOAT(n) format as the data type for SQL.
What should be the values of n?
You can look here for the precision you require https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ve...
For single precision  the value of n=24 should be sufficient. For double precision n =53..

Option 2

Another suggestion, you can convert the numbers to string in SAS and use CHAR(n) of VARCHAR(n) as per your preference. Here n=17 should server your purpose as you are using best17.15.

[JackHamilton]

You don't say how the data are being loaded or how the MS SQL Server table was created, which would be useful information.  But suppose you are creating the table in a data step using SAS/Access:

Use the DBTYPE= data set option to create an MS SQL column of the appropriate type.  An example from the documentation is 

data mydblib.newdept(dbtype=(deptno='number(10,2)' city='char(25)'));
   set mydblib.dept;
run;

There's another option that controls translation in the other direction, DBSASTYPE.  You should read the documentation on both of those options.

I can't say that the documentation for SAS/Access is great - some of it seems designed to remind someone of details they're forgotten rather than to show for the first time - but still, there's a lot of good stuff in there, and you would be well served to read both the MS SQL section and the more general relational database section in the help file.  In Windows, they can be found here, and there's an equivalent online somewhere.