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
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.
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
Nothing strange at all, basic issue in Numeric precision. SAS well documents the maximum integer
The integers are the same for Windows just stored differently.
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
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.
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.