dataset differences //Both,exclusiveA,exclusiveB

[rajeshm]

i am not yet tested your code  But  can you let me know what to do in this below case.

My log:

amount=944.68   /* dataset varible and its value */
indatasetA=1 indatasetB=0  /* these are in-varibles for 2 datasets*/
amount=944.68  
indatasetA=0 indatasetB=1  

my question: here it should be  indatasetA=1 indatasetB=1  but why considering those values as different?

Later  compared  with proc  and got so many as below ,same value but difference is there, thats why i am failing to implement my logic,in this case what to do?

proc compare

                             Obs ||  sumamount  sumamount      Diff.     % Diff                       
                       ________  ||  _________  _________  _________  _________                       
                                 ||                                                                   
                             12  ||     200.39     200.39  2.842E-14  1.418E-14    

[Kurt_Bremser]

Numeric precision; because of the fact that all calculations (and storage) are done in binary, results of different calculation paths (or different imports from a database) can differ in minuscule amounts. To get exact matches, apply the ROUND function.

[rajeshm]

while reading from database into sas , 

I put   length and format+informat   (format+informat 11.2, length 😎

proc sql;
select
AMT   as sumamount format=11.2 informat=11.2 length=8,
from  mytable f
where xx condition;
quit;

it should read from database as 11.2 informat only right ? but after reading into sas. 234.44 is not equal to 233.44 (pulled from another dwh) this is happening for few records only.

Thanks in advance

[Kurt_Bremser]

The format does not change the value; to align values, use the ROUND function, as already suggested.

[rajeshm]

Agreed.

format is for presenting the data and informat is for reading the data, means that i told sas to read in that way.

when i checked my data also it showed me 143.24 like that. but later problem started.

proc sql;
create table mytable as
select
f.TRANSACTION_AMT as sumamount format=11.2 informat=11.2 length=8,
from xxxxxx
quit;

Even when i checked the data it showed me 143.24 in sas but finally faced problems with this kind of issues(143.24!=143.24) .thats why i would like to know. my surprise is for few records it matched but for problem.i confused a lot.

Dont mind for my stupid questions but what i read about informat  is not matching here.

[Kurt_Bremser]

Did you use the ROUND function?

[rajeshm]

I am confused.

Round function is to round the value in sas but while reading data from dwh and clearly indicating to read (with informat 11.2) it should read upto 2 decimal values ex: 143.33 why it is not matching with other datasets 143.33??

i did not use the round function. please explain why informat is not working here?

May be round function is solution but i would like to know importance of informat here..

[Tom]

---

@rajeshm wrote:

I am confused.

Round function is to round the value in sas but while reading data from dwh and clearly indicating to read (with informat 11.2) it should read upto 2 decimal values ex: 143.33 why it is not matching with other datasets 143.33??

i did not use the round function. please explain why informat is not working here?

May be round function is solution but i would like to know importance of informat here..

---

You don't seem to understand what an informat is.  It is instructions for how to convert text into values.  And a format is instructions for how to convert the values into text.

So assuming that DWH means Data WareHouse then an INFORMAT will no affect at all.  Since the INFORMAT is only used when converting text to values. Hence when you are reading from an existing dataset (or view into an external database) no conversion from text is taking place.

Also you don't seem to understand what the informat of 11.2 means. The 11 means you want to read 11 characters.  The 2 means that you want to place an implied decimal point before for the last two digits.  If the text value being read already has a decimal point then the 2 is ignored. Unless you are reading text where the period was purposely removed to save space in the text string there is no need to add a decimal part to an informat.

[Kurt_Bremser]

When you want to make sure that two numbers with fractions in SAS match, you have to use the ROUND function. Depending on the way the numbers were created (reading from a flat file, reading from a database, calculation in SAS) there can be minuscule differences (in the range of 1e-14). Do a Google search for "sas numeric precision" to learn about the details.

Setting the infornat in a SQL SELECT does in fact do nothing, informats come only into play when character data is converted to numbers, either in an INPUT function or an INPUT statement.

[rajeshm]

Thanks. I got clarity with the below statement.

"Setting the infornat in a SQL SELECT does in fact do nothing, informats come only into play when character data is converted to numbers, either in an INPUT function or an INPUT statement."