SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS/Access Issue with Netezza Bigint SAS 9.4

Reply
Regular Learner
Posts: 1

SAS/Access Issue with Netezza Bigint SAS 9.4

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.

 

netezza col

 

 

Super User
Posts: 3,254

Re: SAS/Access Issue with Netezza Bigint SAS 9.4

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? 

SAS Employee
Posts: 215

Re: SAS/Access Issue with Netezza Bigint SAS 9.4

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

Super User
Posts: 5,430

Re: SAS/Access Issue with Netezza Bigint SAS 9.4

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.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 354 views
  • 1 like
  • 4 in conversation