Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Odd Behavior on Sum (Getting Numeric Overflow)

Tags:

sas

teradata

I was working with a SAS proc sql query and uncovered something odd. First, I tried this simple query:

proc sql;
  CREATE TABLE test AS 
    (SELECT
       YEAR(dt) AS yr,
       MONTH(dt) AS mo,
       SUM(val) AS total
     FROM
       mydb1234.myTable
     WHERE
       myDate BETWEEN x AND y
     GROUP BY
       yr, mo);
run;

When I run this query, I receive this error:

ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

From what I can tell, this happens when my summed value gets too large to fit in the data type that proc sql is trying to use.

I decided that I would just divide the number I am summing:

SUM(val/1000) AS total

However, this had unintended consequences. The summed total was less than a manual sum I did in Excel. The total gets lower as I add more orders of magnitude to the divisor. I'm guessing this is eliminating smaller values that it tries to sum (ex. 10/1000 vs 108/10000, etc) that never reach the sum and are instead read as zeros.

Is there a way to force this proc sql to create a table using a field length that can accomodate my total values? They are in the billion-hundred billion range, so it's nothing that unusual I would think. I was curious to see what you guys thought.

like image 427
Jeffrey Kramer Avatar asked Jul 25 '13 19:07

Jeffrey Kramer


People also ask

How do I fix numeric overflow?

The error normally occurs if the value which is SELECTED or value to be inserted has more length than specified. So to resolve the issue with 'Numeric Overflow' error, run the failing query directly on database first and check if any of the output value has more length/digit than specified and then correct that value.

What causes numeric computation overflow?

In computer programming, an integer overflow occurs when an arithmetic operation attempts to create a numeric value that is outside of the range that can be represented with a given number of digits – either higher than the maximum or lower than the minimum representable value.


2 Answers

I suspect what is happening is the query is getting pushed back onto Teradata via implicit pass-through, and something doesn't work in Teradata as a result. It's possible that you need the query to explicitly cast the sum as something other than what it is being cast as.

To see what's happening exactly, use OPTIONS SASTRACE; from the documentation it's suggested to try

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

although you may need to muck around with the options some. This will show you the exact query that is performed in Teradata. Try that same query directly in Teradata, and see if you can prevent it from having the same issue.

Once you've figured that out, you can perform the correct query using explicit pass-through; ie

proc sql;
 connect to teradata [options, same as on the libname usually];
 create table mydata as select * from connection to teradata (
   ... actual teradata syntax ...
 );
quit;
like image 72
Joe Avatar answered Sep 28 '22 10:09

Joe


I'm going to investigate the underlying problems that caused this issue as Joe pointed out before. However, I found a quick workaround that solves the root issue. I used the following line for my SUM

Round((SUM(myField))/1) format=13. 
like image 28
Jeffrey Kramer Avatar answered Sep 28 '22 08:09

Jeffrey Kramer