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

Hi,

I have two table,

one has this value: a=2894378 and the second has this value: b = 28943.78.

I make the join between them, and when i verify if they are equal :

c = a / 100

if  c = b 

sas interprets like different value.

I try to change also the format, but after there are only 0 value.

 

I don't understand why...

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

Could be a numeric precision issue.

 

Try checking something like: if abs(c-b) < .000001   

 

Then google / search lexjansen.com for SAS numeric precision.

View solution in original post

5 REPLIES 5
flyover
Calcite | Level 5
Maybe try the other way around.
C = b * 100
Check if c = a
Quentin
Super User

Could be a numeric precision issue.

 

Try checking something like: if abs(c-b) < .000001   

 

Then google / search lexjansen.com for SAS numeric precision.

ballardw
Super User

What is the actual FORMAT of the value when b=28943.78?  A format will display values as requested and may often round values. It may be the data actually has more decimals stored but you haven't examined them.

 

Run this program and examine the results:

data _null_;
  file print;
  do x=28943.778 to 28943.786 by 0.001;
     put "Format 10.3: " x= 10.3 " Format best8.: " x= best8.;
  end;
run;
FreelanceReinh
Jade | Level 19

Hi @Rakeon,

 

I suspect that the exact value of b is not equal to 28943.78, although it might be displayed as 28943.78 with common formats such as BEST12.. Tiny deviations from the internal standard representation of a numeric value can easily result from calculations or data import from other platforms, external databases, etc.

 

When I compare numeric values where this might be an issue I usually apply the ROUND function with a suitable rounding unit to either or both sides of the equals sign (see code example below). Other options include checking the difference (as suggested by @Quentin) or using the COMPFUZZ function.

 

It's also good practice to remove those tiny errors as soon as they occur. That is, apply the ROUND function to affected variables after data import from an external database or to the result of a calculation (e.g., total = round(amount1 + amount2, 0.01) if amount1 and amount2 have up to two decimals).

 

On Windows (or Unix) SAS I'd expect a quotient of a 7-digit integer divided by 100 to be "clean" in the sense that it should be equal to the corresponding decimal fraction with two decimals. (Please let me know if you find a counterexample on a different platform.) However, multiplying a decimal fraction with two decimals by 100 will not be equal to the expected integer in a substantial number of cases, e.g, 0.07*100 ne 7 is true.

 

Example:

622   data _null_;
623   a=2894378;
624   b=28943.78;
625   c=a/100;
626   d=20000.08+8943.70;
627   e=b*100;
628   f=d*100;
629   put (a--f) (=hex16. /);
630   put (d f) (=best12. /);
631   if b=round(d,1e-9) then put 'OK';
632   diff=d-b;
633   put diff=;
634   run;

a=4146151500000000
b=40DC43F1EB851EB8
c=40DC43F1EB851EB8
d=40DC43F1EB851EB9
e=4146151500000000
f=4146151500000001
d=28943.78
f=2894378
OK
diff=3.637979E-12

 

The HEX16. format is ideal for diagnosing such issues. As shown in the log above, the calculated value d is slightly too large (by 2**-38=3.6...E-12) due to a rounding error, but common formats (even BEST32. or 32.26) don't reveal this error because they slightly round values. The equality e=a holds in this particular case, but could fail with similar numbers. Variable f shows the risk of not caring about "unclean" values such as d: The rounding error has propagated and now affects what should actually be an integer value (so that a ne f). Again, the seemingly clean BEST12. display is treacherous. The rounding unit 1E-9 is greater than diff and also small enough to preserve relevant decimals. So it is suitable for removing this error.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1877 views
  • 3 likes
  • 5 in conversation