Re: Comparison problem in SAS dataset derived from Teradata

mvalsamis

I need to derive a table in SAS Viya that is derived from Teradata external database. Then I need to create another table were I compare two variables of the former table, but the comparison is not done correctly. Specifically, I may have 2 equal values, but they are regarded as unequal. Let me write a simplified example to explain you:

proc sql;
  connect to teradata (<credentials>);
  create table work.derived as
  select id informat=6. format=6. length=8,
            total_amount informat=19.2 format=19.2 length=8,
            mean_amount informat=19.2 format=19.2 length=8
  from connection to teradata (
  select top 1 teradata_view."id",
            teradata_view."total_amount",
            teradata_view."mean_amount"
  from "teradata_catalog"."teradata_view"
  where date='2025-09-09');
  disconnect from teradata;
quit;

The table WORK.DERIVED is generated (example):

id	total_amount	mean_amount
567912	7.38	7.38

Then, I need to create the table WORK.COMPARISONS

data work.comparisons;
  set work.derived;
  if total_amount > mean_amount then flag=1;
  else flag=0;
run;

The table WORK.COMPARISONS is created as follows:

id	total_amount	mean_amount	flag
567912	7.38	7.38	1

As you see, flag=1, which is wrong. It should be flag=0. I tried increasing the decimals in the format and informat (e.g. 23.4) or remove the format and informat, but the results are exactly the same. Do you have any idea where this error comes from and how I can best solve it? Thank you in advance!

Tom

So the value in TOTAL_AMOUNT is larger than the value in MEAN_AMOUNT, but the difference is so small that both numbers print the same when display with only two decimal places.

This is NORMAL behavior when working with non-integer values. Computers store data using BINARY (base 2) floating point. So many fractions like 38/100 which can be represented exactly in base 10 cannot be represent exactly.

ROUND() both numbers.

 if round(total_amount.0.001) > round(mean_amount,0.001) then flag=1;
  else flag=0;

mvalsamis

Let me try it, thank you.

FreelanceReinh

Fully agree with Tom. I would expect that those values would be judged unequal even within Teradata, i.e., the transfer to SAS has not caused the difference. Note that the same sort of numeric precision issues can easily occur in SAS, too. Tom's solution would be the remedy here as well.

Example:

288   data _null_;
289   total_amount=7+0.11+0.27;
290   mean_amount =7+0.27+0.11;
291   if total_amount > mean_amount then flag=1;
292   else flag=0;
293   rflag=(round(total_amount,1e-9) > round(mean_amount,1e-9));
294   put (_all_) (=best32.);
295   put (_all_) (hex16. /);
296   run;

total_amount=7.38 mean_amount=7.38 flag=1 rflag=0
401D851EB851EB86
401D851EB851EB85
3FF0000000000000
0000000000000000

Note that even the BEST32. format conceals the tiny difference (of 2**-50=8.881...E-16) between total_amount and mean_amount, which arose from just adding three numbers in different orders and which is reliably revealed by the HEX16. format.

Comparison problem in SAS dataset derived from Teradata