DECIMAL value out of range

I am trying to publish data from our SAS environment into a remote Hadoop/Hive database (as sequence files). I'm performing basic tests by taking some source data from our business users and using a data step to write out to the Hadoop library.

I'm getting errors indicating that a value at row X is out of range.

For example:

ERROR: Value out of range for column BUY_RT1, type DECIMAL(5, 5). Disallowed value is: 0.

The source data has a numeric format of 6.5, and the actual value is .00000.

Why is .00000 out of range? Would the format for Hadoop need to be DECIMAL(6, 5)?

I get the same error when the value is 0.09:

ERROR: Value out of range for column INT_RT, type DECIMAL(5, 5). Disallowed value is: 0.09

1 answer

  • answered 2018-10-12 09:51 Chris Long

    You may need to check the actual values in SAS. If a numeric value in SAS has a format applied, you will see the formatted (possibly rounded) version of the numeric value wherever you output the value, but the underlying numeric may still have more significant digits that you're not seeing, due to the format.

    For example, you say your source data has a format of 6.5 and the 'actual value' is 0.00000; are you sure that's the actual value? To check, you could try comparing the value to a literal 0, or putting the value to the SAS log with a different format like BEST32. (eg put BUY_RT1 best32.;).

    If this is the problem, the solution is to properly round the source numeric values, rather than just applying a format.