topic Re: SAS data into Ms SQL in SAS Data Management

SAS data into Ms SQL

Citrine10 — Thu, 26 Aug 2021 13:42:09 GMT

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?

Re: SAS data into Ms SQL

Sajid01 — Thu, 26 Aug 2021 14:17:38 GMT

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

Re: SAS data into Ms SQL

JackHamilton — Thu, 26 Aug 2021 18:22:14 GMT

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.

Re: SAS data into Ms SQL

Citrine10 — Fri, 27 Aug 2021 14:01:22 GMT

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.

Re: SAS data into Ms SQL

Sajid01 — Fri, 27 Aug 2021 14:29:05 GMT

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