Floating Point Numbers between Redshift and SAS

[gra_in_aus]

Hi, 

We have data in Redshift that contain floating point numbers (e.g. 0.924780).  My understanding is traditional SAS data step or PROC SQL would not be able to handle the data without rounding.  I was wondering if the data would be displayed in SAS datasets when using a simple query (i.e. where xxx = 0.924780) from SAS against Redshift using PROC FEDSQL or DS2?  We are currently using an AWS Redshift ODBC driver to connect between SAS and Redshift.

Also, I have difficulty connecting FEDSQL and DS2 to ODBC connections and keep getting PROC FEDSQL initialization failed errors, which are not very informative at all.

LIBNAME _all_ clear;

PROC FEDSQL NOLIBS CONN="DRIVER=FEDSQL;CONOPTS=((DRIVER=ODBC;DB=REDSHIFT101;UID={&uid.};PWD={&pwd.};CATALOG=RS_PROD; SCHEMA=MY_SCHEMA))";
CREATE TABLE temp_gr AS
SELECT *
FROM RS_PRODC.MYREDSHIFT_DATA;
QUIT;

NOTE: Connection string:

NOTE: DRIVER=FEDSQL;CONOPTS= ( ( (DRIVER=ODBC;DB=REDSHIFT101;UID={*};PWD={*};CATALOG=RS_PROD;SCHEMA=MY_SCHEMA)))

ERROR: ERROR: [

ERROR: PROC FEDSQL initialization failed.

Many thanks

G

[SASKiwi]

SAS 9.x can handle up to 15 significant digits. So for numbers between 0 and 1 that means 15 decimal places. Why do you think you need more? It's not about rounding, its about the number of digits that can be stored accurately. That goes for both Redshift and SAS.

[gra_in_aus]

Business requirement wants to have this level of precision.  They want to be able to use WHERE and IF/THEN/ELSE logic against the columns with floating point issues.  I have checked  with the Redshift developers and they noted no issues when trying to use CASE WHEN statements on the column.

[SASKiwi]

What is "this level of precision"? Please define what you mean. Please post examples of the problems you have found. 

[Patrick]

SAS uses 8 bytes to store numbers. A floating point like 0.924780 can be stored with full precision.

Having said that: Like 1/3 in a decimal system, some floating point numbers can't get represented absolutely exact in a dual system, i.e. 0.1. 

Different platform/chipsets store such numbers differently and when moving floating point data from one platform to another you can end up with some differences in the very last bits. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p0dv87zb3bnse6n1mqo360be70qr.htm 

This is just how computers work so nothing special about SAS. 

What it means: If you want to use a where clause on floating point numbers that have been sourced from another platform then round the number to a precision higher than what you compare to. 

For example: where round(myvar_from_redshift,0.000000000001) = 0.924780

[gra_in_aus]

Thanks Patrick, would this be the same with IF statements? Also, I thought DS2 and FEDSQL were introduced to deal with the complexities of extracting data from databases and to provide greater precision, am I correct? I was hoping that the procedures would assist were standard data step and PROC SQL are not able to assist.

Thanks
G

[Patrick · Accepted solution]

---

@gra_in_aus wrote:
Thanks Patrick, would this be the same with IF statements? Also, I thought DS2 and FEDSQL were introduced to deal with the complexities of extracting data from databases and to provide greater precision, am I correct? I was hoping that the procedures would assist were standard data step and PROC SQL are not able to assist.

Thanks
G

---

This is not really a precision issue but how computers store floating point numbers and the differences between platforms.

You find in the already shared SAS Docu link the following:

There are floating point numbers which you just can't store with 100% precision on a finite system - and that's where you might encounter differences when transferring such a number from one platform to another. 

The only way to get around this - not only with SAS but any software - is either rounding or casting the number to text in the source system and then cast back to a number in the target system.

And just thinking - never tested it - eventually bulk load and bulk unload will return the result you intuitively expect. I'm not sure if true for all databases but at least for Oracle I believe the intermediary file exchanged between the platform is a text file and though this number representation bit should "go away".