Postgres Data "Infinity" in Double type is read as funny value in SAS dataset

[imdickson]

Hi SAS Communities.

I have a script that read from Postgres SQL in Datastep. However, upon checking, i notice that there are a few Double Precision columns in PostgresSql are having the value of "Infinity".

I tried to put the logic:
if missing(CashValue) or CashValue=. then CashValue=0

but it is not converting it to 0 but remain 0.000000E309, which is wrong for sure.

Does anyone have similar issue or can provide advice on this issue?

[ChrisNZ]

Some things are not clear.

1. 0.000000E309  is zero. Is it what you see in SAS? Thats strange.

2. 0.000000E309  is  not missing, so your test would fail

3. SAS show Infinity as special missing value I

See it by running:

data A; A=divide(1,0); run;

4. What's the value held in Postgres SQL for one of these?

5. I had to deal with a bug on Teradata, and worked around it by dividing the value by 1e6 in the pass-through SQL, and multiplying again.

[Tom]

So it looks like PosgreSQL is trying to implement IEEE +infinity and/or -infinity floating point values.  

From the Wiki page https://en.wikipedia.org/wiki/IEEE_754-1985#Positive_and_negative_infinity 

Positive and negative infinity[edit]

Positive and negative infinity are represented thus:

sign = 0 for positive infinity, 1 for negative infinity.

biased exponent = all 1 bits.

fraction = all 0 bits.

Since the floating point number is made up of 1 bit sign, 11 bit exponent and 52 bit fraction then that means value should be 

We see that those should be represented by Hex strings of 7FF000000000000 and FFF0000000000000.

But those don't display as 0E-309. How ever if we reverse the bits and use 0000000000000FFE and 0000000000000FFF instead then SAS does display those that way.  So it looks like somehow the values are either stored in Posgresql backwards or SAS reverses the bits?

Run a test.  Make a little table in the database with a floating point number and store +infinity and -infinity into two records. Pull it into SAS and display the values with the HEX16 format.

[FreelanceReinh]

Hi @imdickson,

Please show us at least one of those "E309" values in HEX16. format (as has been requested already). Having followed this and the other thread, I'm really curious how they look like. Where do you see those values actually? In PROC PRINT output? Or in a Viewtable window? Isn't it possible to identify one of the affected records either by its observation number or by a suitable (i.e. unique or rare) value of another variable, e.g. an ID? Then you could simply write something like this:

proc print data=have(obs=3);
where id= /* put ID value here */;
var CashValue;
format CashValue hex16.;
run;

Another relevant information is your SAS platform: Is it a Windows/Unix system or a mainframe or something else?

On my Windows machine I found that values from 0000000000000001 to 0000000000000014 (in HEX16. format) are displayed as 0.000000E-309 (with varying numbers of zeros; note the minus sign) with standard formats such as BEST32. or E32. For larger values (i.e. "...00015" etc.) non-zero digits start to appear (with suitable formats) and these digits make sense mathematically (IEEE-754 standard; the implied bit is not used in these special cases!). Interestingly, 8000000000000000 (HEX16.), i.e., kind of "minus zero", is displayed as 0.000000...E-309 as well with the Ew. format, but at the same time it's regarded equal to 0.

I've not yet been able to produce a number that is displayed as 0.000000E309.

@Tom: I think 0000000000000FFF and the like are rather "ordinary" small numbers. At least on Windows systems this particular example should be (2**-39 - 2**-51) * 2**-1023 = 2.02...E-320, which is correctly displayed (rounded) as 0.0000000000202E-309 in E20. format.

[imdickson]

There is no solution even from SAS technical support on this causing catastrophic error to VA. What I did was using pass thru to convert Infinity to 0. Case Closed.

[ChrisNZ]

Thanks for keeping us updated.

[SimonDawson]

@imdickson I recall seeing a bug ticket about this the other day. It was for a customer in Asia. Having a NaN value in your data causes a known issue in LASR that has been there a long time. It only recently was seen being triggered by a customer. Both cases I've seen now involved data in LASR being loaded from PostgreSQL to LASR and a +infinity was in the table. It is actively being looked at by R&D.