topic Re: oracle variable not showing format and informat in SAS, the value change in proc compare process in SAS Data Management

oracle variable not showing format and informat in SAS, the value change in proc compare process

Jade_SAS — Fri, 10 Nov 2017 21:34:25 GMT

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

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Tom — Fri, 10 Nov 2017 22:01:33 GMT

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?

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

TomKari — Fri, 10 Nov 2017 22:09:46 GMT

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

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Reeza — Fri, 10 Nov 2017 22:26:48 GMT

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

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Patrick — Sat, 11 Nov 2017 10:57:46 GMT

@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&docsetVersion=9.4&locale=en

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docsetVersion=9.4&locale=en

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Jade_SAS — Wed, 15 Nov 2017 14:31:06 GMT

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

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Jade_SAS — Wed, 15 Nov 2017 14:38:23 GMT

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.

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Jade_SAS — Wed, 15 Nov 2017 14:41:23 GMT

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!

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Tom — Wed, 15 Nov 2017 14:45:40 GMT

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.

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Jade_SAS — Wed, 15 Nov 2017 14:45:27 GMT

Thank you, Tom, I will try it.

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Tom — Wed, 15 Nov 2017 15:00:55 GMT

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

Re: oracle variable not showing format and informat in SAS, the value change in proc compare process

Jade_SAS — Wed, 15 Nov 2017 16:05:51 GMT

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!