Odd Behavior on Sum (Getting Numeric Overflow)

Question

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.

This isn't a SAS specific issue, I don't think. SAS won't give you an overflow; unless you're in such insane numbers that it couldn't be stored imprecisely in 64 bits, anyway, which is certainly not the case. If you generated a number of > 15 significant digits, it might start lopping off significance, but it won't give you any indication it's doing so. — Joe, Jul 25 '13 at 19:40
How is the interface with teradata working here? Is there a `libname` to a teradata server, or is it pass through, or something else? — Joe, Jul 25 '13 at 19:40
There's a library and I reference it like this: `FROM mydb1234.myTable` Where mydb1234 is a `libref` — Jeffrey Kramer, Jul 25 '13 at 19:44
The totals are in the hundreds of billions per year, nothing extraordinarily large. — Jeffrey Kramer, Jul 25 '13 at 19:48
Please show the LIBNAME statement used; there are various Teradata-specific options that might help. — BellevueBob, Jul 25 '13 at 23:26
What is the data type of the column being aggregated? @Joe's answer alludes to the fact you have exceeded the bounds of the data type for the column being aggregated. `CAST(SUM(VAL) AS {larger data type});` — Rob Paller, Jul 26 '13 at 04:18

score 5 · Answer 1 · answered Jul 25 '13 at 19:50

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;

score 1 · Accepted Answer · answered Jul 25 '13 at 21:29

1

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.

answered Jul 25 '13 at 21:29

Jeffrey Kramer

1,345
6
25
43

score 0 · Answer 3 · answered Jul 26 '13 at 10:05

0

In these kind os situations it is always the data type that has been defined for the val column in your Teradata table that is causing the 'numeric overflow' problem. (I'm assuming val is defined as Integer type which can hold upto +/-2.1billion)

Try this,

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

In the above code, SUM(cast(val as dec(32,0))) is first converting (casting, officially) the val column to a datatype which can hold more than a few billion and then summing. The summed column total will be of dec(32,0) and SAS is more than capable of handling such large numbers.

answered Jul 26 '13 at 10:05

I don't think that would compile in SAS, since cast is not a SAS function, would it? It would work in explicit passthrough. – Joe Jul 26 '13 at 13:37
If you execute the SQL as pass through (leveraging in database processing) to Teradata the SQL in this answer will run directly on Teradata. – Rob Paller Jul 26 '13 at 14:12
Certainly, but the above answer does not do that. :) Most of the time 'leveraging in database processing' is not very important as SAS largely does this anyway (converts SAS code to in-database processing); that after all is what's getting us into trouble in the first place here. – Joe Jul 29 '13 at 17:44

Odd Behavior on Sum (Getting Numeric Overflow)

3 Answers3