BookmarkSubscribeRSS Feed
Jade_SAS
Pyrite | Level 9

Hi All,

 

    I have one table from Oracle table, and there is one variable has float type,  the value is with two digits in original oracle table(for example 14.78).

    If I use SQL pass through, I saw the value in SAS table is with two digits (for example 14.78), but the format and informat are blank in SAS.

    Then if I use SAS proc compare, this value is different from my Teradata data which is 14.78, because right now the value in proc compare result is 15 for this oracle variable instead of 14.78.

    Is there anyone know the reason? Thank you!

 

Jade

11 REPLIES 11
Tom
Super User Tom
Super User

FORMAT and INFORMAT are optional metadata information. They do not change the actual data in any way and so should not have an impact on whether PROC COMPARE sees and difference in the value.  The FORMAT and INFORMAT just tell SAS how you want it to print or read the value as text.  But SAS already knows how to do that for numeric AND character variables. 

 

Also note that in SAS all numeric variables are stored as IEEE floating point numbers.

 

So if your query from Teradata produces 15 instead of 14.78 then the problem it probably on the Teradata side.

Did you define the variable as INTEGER in Teradata?

Jade_SAS
Pyrite | Level 9

Thank you! The Oracle one is giving me trouble, and the format for this variable is float  on Oracle.

Tom
Super User Tom
Super User

The most likely causes of a value looking like 15 instead of 14.78 is rounding.  So if your SAS variable does not have a format attached that would do the rounding then the value was either rounded by the way it was calculated or perhaps rounded by the way SAS pulled it from Oracle.

 

Show the code that you used to pull the data.  Preferable you can make a simple example that recreates the problem. 

 

You might even be able to do this without referring to any datasets in Oracle or Teradata. In TERADATA you can write a SELECT without any FROM clause.  What happens if you do this?

select * from connection to teradata
(select 14.78/1.0 as floatvar, 1478/100 as intvar )
;

In ORACLE you can do something similar by selection FROM DUAL.

 

 

 

TomKari
Onyx | Level 15

1. Can you share the code that you are using to create your table from Oracle using SQL pass-through? (feel free to change the names if they're confidential.)

 

2. How do you know that there's no format or informat on the SAS dataset that you're creating from Oracle?

 

Tom

Jade_SAS
Pyrite | Level 9

Sure.

1) Proc SQL;

    Create table Ora_data as

    select * from oralib.table1;

Quit;

2) When I do the PROC compare process, it will show the variable format of the base table and compare table, so I can see the format of the original oracle table did not get carried on to SAS.

Tom
Super User Tom
Super User

@Jade_SAS wrote:

Sure.

1) Proc SQL;

    Create table Ora_data as

    select * from oralib.table1;

Quit;

2) When I do the PROC compare process, it will show the variable format of the base table and compare table, so I can see the format of the original oracle table did not get carried on to SAS.


I doubt that SAS changed the value when moved from Oracle in that manner. If you see 15 and you expected 14.78 then check that process in Oracle that create TABLE1.

 

What do you mean by "format of the original oracle table"?  In SAS a FORMAT definition is just instructions to be used by SAS when translating the value stored in the variable to text when you print it.

 

In SAS every variable is either a fixed length character string or a floating point number.  In Oracle you can have a large variety of variable types, but they all need to be mapped to one of the two types that SAS supports.

 

So for example SAS/Access to Oracle knows how to translate Oracle DATE value to SAS datetime values and will automatically attach a DATETIME format to the variable so that it will look like a datetime value when you print it.  Similarly if you have a variable defined in Oracle as DECIMAL then SAS will try to attach a proper format for displaying the floating point value it stores with the same number of digits. 

 

In Oracle you can define many more variable types.  Does Oracle also have a concept that is similar to the SAS FORMAT concept?

Jade_SAS
Pyrite | Level 9

I want to clarify that In step1 (the SQL Pass through), the value I see from SAS is 14.78, but while I am doing the step2 (PROC Compare), the value is 15.

Thank you very much for the inputs!

 

Reeza
Super User

Make sure to see the FUZZ option within PROC COMPARE otherwise you may get small results that show as significant.

Jade_SAS
Pyrite | Level 9

I tried with criteria=0.001 and the values are considered as equal now, but with FUZZ=0.001 it did not work for me. Thank you!

 

Patrick
Opal | Level 21

@Jade_SAS

Using the FUZZ option as @Reeza suggests is important when comparing floating point data sourced from different platforms (i.e. Oracle and SAS).

Be aware that there are often small differences in the last bits for such scenarios and that the only way to get around this is to either round() your values or use the FUZZ option.

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1gv8bfdnhvy9yn1cj8g4di2eqg7.htm&docsetV...

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docsetVer...

 

 

 

 

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!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 11 replies
  • 1355 views
  • 0 likes
  • 5 in conversation