topic Re: SAS Not inserting/Appending decimal values exact in Snowflake in SAS Data Management

SAS Not inserting/Appending decimal values exact in Snowflake

bverma — Wed, 26 Feb 2020 17:25:06 GMT

Issue while Inserting decimal value in Snowflake by selecting form SAS, having difference of penny most of the time. Seems like that snow is ceiling down to -.1. Please see AMT1 and AMT2 columns in following examples and advise.

Is there something I need to set on table level or a special format I need to apply before I insert it?

Data from SAS

### xxx AMT1 AMT12 AMT3
203507725 57.97 39.99 17.98
203507728 95.97 75.98 19.99
203507731 57.97 39.99 17.98
203507732 77.97 59.99 17.98
203507733 67.97 49.99 17.98
203507736 188.96 165.98 22.98

Data showing/Appending in Snowflake

### xxx AMT1 AMT12 AMT3
203507725 57.96 39.99 17.98
203507728 95.96 75.98 19.99
203507731 57.96 39.99 17.98
203507732 77.96 59.99 17.98
203507733 67.96 49.99 17.98
203507736 188.95 165.97 22.98

Re: SAS Not inserting/Appending decimal values exact in Snowflake

Tom — Wed, 26 Feb 2020 17:37:25 GMT

SAS only has floating point numbers. Is your SNOWFLAKE data (or more importantly your calculations in SNOWFLAKE) using fixed decimal data type?

If you want to implement fixed decimal calculations in SAS then it might be best to store the numbers as integers. So instead of storing $57.97 as 57.97 store it as 5,797. Note that if you do any operations other than addition (or subtraction) then you will also need to decide how to round the results.

Re: SAS Not inserting/Appending decimal values exact in Snowflake

bverma — Wed, 26 Feb 2020 18:34:37 GMT

I am selecting data from sas data set but I have couple of queries in between and getting final output in work query. AS advised I have converted column AMT1 into integer by using int function i.e. (INT(AMT1)) which changed it to absolute value basically e.g. 57.00 vs 57.97 I want to load exact value 57.99

### xxx AMT1 AMT12 AMT3

203507725 57.00 39.99 17.98
203507728 95.00 75.98 19.99
203507731 57.00 39.99 17.98
203507732 77.00 59.99 17.98
203507733 67.00 49.99 17.98

203507736 188.00 165.97 22.98

Re: SAS Not inserting/Appending decimal values exact in Snowflake

TomKari — Wed, 26 Feb 2020 19:41:19 GMT

Interesting problem.

Is it possible to do the following?

1. Display a few of the values that are of concern in SAS showing the full 15 significant digits.

2. Have these variables uploaded to Snowflake in the usual way.

3. Download them to SAS again, and again display the full 15 significant digits.

It would be good to see how much the variables are changing, as opposed to truncating/rounding for display.

Tom

Re: SAS Not inserting/Appending decimal values exact in Snowflake

bverma — Fri, 10 Apr 2020 16:48:44 GMT

I recreated tables with float, instead of numeric with precision value, worked for me.

Re: SAS Not inserting/Appending decimal values exact in Snowflake

SASKiwi — Fri, 10 Apr 2020 22:04:33 GMT

@bverma - Please mark your post as answered in that case.