topic Re: Large integer field format in SAS in SAS Programming

Large integer field format in SAS

jromeo — Mon, 10 Apr 2017 17:41:44 GMT

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

Re: Large integer field format in SAS

art297 — Mon, 10 Apr 2017 17:59:28 GMT

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

Re: Large integer field format in SAS

ballardw — Mon, 10 Apr 2017 17:59:50 GMT

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.

Re: Large integer field format in SAS

Tom — Mon, 10 Apr 2017 20:07:37 GMT

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.

Re: Large integer field format in SAS

jromeo — Mon, 10 Apr 2017 20:40:13 GMT

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!

Re: Large integer field format in SAS

rogerjdeangelis — Mon, 10 Apr 2017 20:54:56 GMT

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

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