Large integer field format in SAS

[jromeo]

We have been using data from Hadoop via Hive ODBC in SAS and noticed something strange.   A large integer field is returning different results in SAS than when the same query is run in HUE.  I’ve done some testing and it looks to me like a SAS format issue, but I can’t figure out how to resolve.  Have you seen this before?   Here is an example:

Pull riskid from Hue (9176485618548934709) and put in a SAS where statement (see below) and you return 9176485618548934656 instead of 9176485618548934709

[art297]

My guess would be numeric precision. You didn't show the statement but, given the size of that number, I'd think it would have to be stored as a character variable. Most computers can't handle a number with that many digits.

Art, CEO, AnalystFinder.com

[ballardw]

Nothing strange at all, basic issue in Numeric precision. SAS well documents the maximum integer

Significant Digits and Largest Integer by Length for SAS Variables under UNIX

Length in Bytes	Significant Digits Retained	Largest Integer Represented Exactly
3	3	8,192
4	6	2,097,152
5	8	536,870,912
6	11	137,438,953,472
7	13	35,184,372,088,832
8	15	9,007,199,254,740,992

The integers are the same for Windows just stored differently.

[rogerjdeangelis]

Have not had this problem in a while but using passthru you should be able to cast the big int.

if

   cast(bigint_var,char(20) or decimal(20) ) you don't need to go any larger  because bigint max is

9,223,372,036,854,775,807

[Tom · Accepted solution]

Is there a way that you can pull the RISKID field as CHARACTER from Hadoop?  SAS cannot handle values that are too large to represent uniquely using IEEE 8 byte floating point numbers. If it really is an ID variable then it should be character anyway since no one will be doing any arithmetic on it.

[jromeo]

Yes - it should be a character field.  I am not sure it is being read as numeric.  I'll try to convert the format thank you!