BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gra_in_aus
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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:

Patrick_1-1648597480916.png

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

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

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
Quartz | Level 8

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
PROC Star

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

Patrick
Opal | Level 21

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
Quartz | Level 8
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
Opal | Level 21

@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:

Patrick_1-1648597480916.png

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 873 views
  • 2 likes
  • 3 in conversation