topic Re: SAS/Access Issue with Netezza Bigint SAS 9.4 in SAS Data Management

SAS/Access Issue with Netezza Bigint SAS 9.4

suneeth8 — Tue, 12 Jul 2016 07:01:39 GMT

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/Data-Types

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.

Re: SAS/Access Issue with Netezza Bigint SAS 9.4

SASKiwi — Tue, 12 Jul 2016 07:31:30 GMT

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#p07ge3f7fvfh5ln1msgdpqhbeh31.htm

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?

Re: SAS/Access Issue with Netezza Bigint SAS 9.4

JBailey — Tue, 12 Jul 2016 12:55:23 GMT

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

Re: SAS/Access Issue with Netezza Bigint SAS 9.4

LinusH — Wed, 13 Jul 2016 22:12:26 GMT

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.