BookmarkSubscribeRSS Feed
suneeth8
Calcite | Level 5

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

 

 

3 REPLIES 3
SASKiwi
PROC Star

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
Barite | Level 11

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

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1287 views
  • 1 like
  • 4 in conversation