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
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.
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
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
I recreated tables with float, instead of numeric with precision value, worked for me.
@bverma - Please mark your post as answered in that case.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.