problem with where clause on numeric

[Nasser_DRMCP]

data have ;
input NUM_ENR_IXN ;
format NUM_ENR_IXN 20. ;
informat NUM_ENR_IXN 20. ;
datalines ;
7572522571591138304
;
run ;

hello

I create this "have" table . and then, in the where clause of sas i specify NUM_ENR_IXN=7572522571591138304. and the row is retreived.

But, I try do do the same where in a table that hold 4 milions rows and it retreive no row. yet, i can see this value displayed in the first row.

thanks in advance for your help

nass

[andreas_lds · Accepted solution]

The number is to large. See https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/hostunx/p12zsdbylnn6c2n1i48z7djr6uzo.htm for details.

The largest integer sas can represent exactly is 9,007,199,254,740,992.

[Kathryn_SAS]

Can you confirm that the variable is numeric in your real data set by running a PROC CONTENTS? Can you send your log? Because of floating point representation, SAS can only guarantee precision up to 15, and sometimes 16, significant digits. It may be better to read this variable in as character and then use quotes around the value in your WHERE clause.

[Nasser_DRMCP]

i confirm that this is a numeric. i have no log.

i notice that the value appaers by doing a where cluase like  NUM_ENR_IXN>=7572522571591138000 and NUM_ENR_IXN <= 7572522571591138999

[FreelanceReinh]

Hello @Nasser_DRMCP,

---

@Nasser_DRMCP wrote:

But, I try do do the same where in a table that hold 4 milions rows and it retreive no row. yet, i can see this value displayed in the first row.

---

A numeric value like 7572522571591139328 (an integer with 19 decimal digits) in a SAS dataset has a high risk of being the result of unintended rounding in the binary system. Note that this number is divisible by 2**14=16384, hence it has 14 trailing zeros in the binary number system.

I suspect that those four million "rows" of data that you mention are not an ordinary SAS dataset (under a Windows or Unix OS), but a table, e.g., in CAS or from an external database where special data types such as BIGINT are available. Those are supported by DS2 and FedSQL, but not through the SAS BASE driver. This could explain why a WHERE clause using the rounded value does not retrieve any rows.

Working with numeric values so close even to the limits of the BIGINT data type is risky, as your example shows. So, unless you ensure that they are handled only by suitable tools (e.g. using explicit pass-through to connect to a data source supporting BIGINT), it might be safer to store them as character values (as @Kathryn_SAS has suggested) or maybe to split them into smaller numeric values. Of course, both these options require some extra considerations.

I would not recommend workarounds that may work in some cases, but not in others. Note that, in an ordinary Windows SAS DATA step, the condition 

@Nasser_DRMCP wrote:

NUM_ENR_IXN>=7572522571591138000 and NUM_ENR_IXN <= 7572522571591138999

---

would be met by numbers that are actually not contained in the specified range:

262   data _null_;
263   if 7572522571591138000 <= 7572522571591137280 <= 7572522571591138999 then put 'Surprise 1';
264   if 7572522571591138000 <= 7572522571591140352 <= 7572522571591138999 then put 'Surprise 2';
265   run;

Surprise 1
Surprise 2

Which is particularly surprising, as the IF conditions above should not be met even if mathematical rounding in the binary system (to 52 mantissa bits) is taken into account.