07-23-2015 02:19 PM
When Iam trying to load data from SAS to SQL decimal values it is rounding off automatically.Eventhough i used same decimal(10,5) same as SQL.
In SAS : 0.31964
In SQL : 0
It is just straight forward load.
INSERT INTO libname.sqltable
from WORK.sastable ;
could any one please advise on this.Thanks in advance !!!
07-23-2015 02:54 PM
There is no decimal(10,5) data type in SAS. All numeric variables are stored in 8 byte floating point. Could it be a mismatch in how the data moves from SAS to SQL? It looks like your values are being truncated because there's an integer data type in there somewhere.
07-23-2015 06:26 PM
Thanks tom for reply.
Please see the example :
Created in SAS :
informat col1 10.5;
format col1 10.5;
When loading to SQL it is loading just 9
there is no conversion between ..
In SQl , The columns datatype is [col1] [decimal](10, 5) NULL
07-23-2015 07:54 PM
Are you using SAS/ACCESS to ODBC then using the SQI ODBC driver? And what is the back-end database you are connecting to?
If so this is what SAS does: http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n13gtugcxgdqstn1po...
07-24-2015 05:20 PM
Everything you're doing looks fine to me.
All I can think of, is can you use a tool native to your DBMS to ensure that the value is actually 9? I'm wondering if it's actually correct in the database, but being truncated when displayed.
07-23-2015 03:38 PM
What is the database you are loading into? SAS uses default translations to convert from SAS columns to database columns. For numerics the SAS format of the column is important, so what SAS formats are applied to the columns being loaded?