SAS/Access Issue with Netezza Bigint SAS 9.4

[suneeth8]

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.

source: https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Netezza-5.-54/page/4/Da...

 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

30              SELECT

31                    SRC_STM_CD,

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.

[SASKiwi]

If you wish to suppress these errors then try the TRUNCATE_BIGINT environment variable as explained here:

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p07ge3f7fvfh5ln1ms...

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? 

[JBailey]

Keep in mind, if you use TRUNCATE_BIGINT=yes then use the column in calculations you will likely get inaccurate results.

[LinusH]

Options msglevel =i sastrace =',,,d' sastraceloc =saslog nostsuffix;
will tell you what's gets passed to Netezza and how any function call will be translated.

If you aren't gonna use it for calculation consider cast it to a char variable, either in a permanent view in Netezza or in a explicit SQL pass through.