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.

Answer 1

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;

Answer 2

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.