BookmarkSubscribeRSS Feed
mmkr
Obsidian | Level 7

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.

PROC SQL;

INSERT INTO libname.sqltable

  (

col1

  ,col2

  ,col3

  ,col4

)

Select

col1

  ,col2

  ,col3

  ,col4

  from WORK.sastable ;

QUIT;

could any one please advise on this.Thanks in advance !!!

6 REPLIES 6
TomKari
Onyx | Level 15

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.

Tom

mmkr
Obsidian | Level 7

Thanks tom for reply.

Please see the example :

Created in SAS :

data xx1;

informat col1 10.5;

format col1 10.5;

input col1;

cards;

9.47663

;

run;

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

ChrisNZ
Tourmaline | Level 20

show us the result of

proc contents data=libname.sqltable;run;

(table used in INSERT INTO libname.sqltable)

SASKiwi
PROC Star

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

TomKari
Onyx | Level 15

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.

Tom

SASKiwi
PROC Star

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2662 views
  • 0 likes
  • 4 in conversation