Defining number of decimals in teradata column during Select statement

Question

In teradata proc sql in SAS Enterprise guide environment, I am trying to create a column by multiplying two fields, but I have been having overflow issue. How do I specify the number of decimals in the resulting field during Select statment? numeric(20,2)

proc sql exec feedback stimer;

connect to teradata as teravw (authdomain=teradataauth TDPID=DWPROD2 MODE=TERADATA SCHEMA=EDW_NOPHI CONNECTION=GLOBAL);

CREATE TABLE WORK.tbl1 AS
SELECT * from connection to teravw
(SELECT

...
    ,case 
        when PCL.CLM_SOR_CD = '512' then cast(round(sum(PCL.PRCG_WHLSL_ALWBL_COST_AMT * CL.PAID_SRVC_UNIT_CNT) ,0.01) as numeric(20,2))
        else SUM(PCL.PRCG_WHLSL_ALWBL_COST_AMT) 
    end AS WAC 
...

);

disconnect from teravw;
quit;
run;

error message:

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

Hard to be sure without information about the underlying column data types, but most likely you need a CAST inside the SUM — Fred
This is a SAS error. If something returned by the SELECT statements cannot be fit into a SAS data type, this error occurs. As @Fred suggests, it needs some type casting / conversion on the returned data. — SAS2Python
20 significant decimal digits is more than SAS can store in 8 byte IEEE floating point values it uses for numbers. Why not just cast the value to FLOAT and return that? — Tom

Tom Tom · Accepted Answer · 2019-07-23T19:18:33

SAS stores all numbers as 8 byte floating point numbers. The maximum number of significant decimal digits is less than 20. Are your values really larger than 9,999,999,999,999.99? Did you try DECIMAL(15,2)?

You can cast the value as FLOAT and attach a format on the SAS side to only display 2 decimal places.

create table my_dataset as
select wac format=20.2 
from connection to teradata (
   select cast( .... as FLOAT) as wac
   from ...
);

If you really need to store 20 decimal digits exactly then you will need to split the value into two fields.

Defining number of decimals in teradata column during Select statement

1 Answers