07-12-2016 03:01 AM
According to SAS documentation on 9.4 version(latest as date), length(max) of numeric variable in SAS data set is 8 bytes or 15 significant digits.
However, in Netezza, numeric-BIGINT can hold up to 18 significant digits.
When we attempt to pull a numeric variable with more than 15 significant digits from Netezza into SAS; as it can’t hold excess of 15 digits, SAS throws either of the below errors:
28 PROC SQL;
29 CREATE TABLE NETEZZA_TEST AS
32 COUNT(PUT(CUST_ID,Z20.)||PUT(CUST_ID,Z20.)) AS CHK_COUNT
33 FROM DATA_Q.D_TABLE;
ERROR: Error during in-database processing.
ERROR: ERROR: ERROR: relation does not exist CCRMPRD.DATA_Q.D_TABLE
The maximum value of CHK_COUNT in above query is 6016788360167883 aka length of 16 digits.
07-12-2016 03:31 AM
If you wish to suppress these errors then try the TRUNCATE_BIGINT environment variable as explained here:
Also I'd suggest being careful with using SAS functions like PUT and SAS formats like Z20. on Netezza as they may not translate correctly. Do you get the same errors with a simple SELECT?
07-13-2016 06:12 PM