EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

SAS error 1: Numerical Value out of range
Error 2: Java Virtual machine exception. Java.lang.outofmemoryerror:Java heap space

SAS Programmer's BookShelf
Question 1: We use to insert some records into a dataset, either in Netezza or other SQL database, and got the following error in the log window:
SAS error: numerical value out of range.

Answer: This usually happened when you have some 'BIGINT' or 'INT' variable in the netezza; if it's converted to double or float format, then there might not be enough length. For example, if you were using proc sql to do the insert, or use the proc append without the option=force, or load the data from netezza to SAS. you would probably get that error.

The most simple solution to drop those 'BIGINT' or 'INT' variables, or use cast function to give them enough length. You need to specify longer length for the base variable. For example, specify the double value instead of the default value, even it's integer value in the base variable:
proc sql;
drop table nz_work.base_data;
      connect to netezza (server='server_name'
      database=database1 user=myuser pwd=mypassword connection=global);
     	execute ( create table base_data
			as select distinct var1,
       cast(1.0 as double) as base_var   

     from data_in as a
distribute on random
;) by netezza;
execute ( insert into base_data  (var1,base_var)
                          select (var1,base_var)
   from new_data 
;) by netezza;

Another common error is that, when using a DATA Step or SQL procedure to insert a BIGINT value greater than 922337203685477580 into a Netezza table, you might see the following ERROR message written to the SAS log:

ERROR: CLI execute error: ERROR: int8 value out of range: "92233***75888"

The table is created, but when it is accessed, this message is issued:
ERROR: Invalid Operation. ERROR: Termination due to Floating Point Exception

To work around the error, either do not use values larger than 19 digits, or else you can convert the values to character, as follows:

proc sql; 
create table nz.newtable 
as select put(t1.i, 20.) as new_i 
from work.test as t1 
; quit; 

ERROR 2: Java Virtual machine exception. Java.lang.outofmemoryerror:Java heap space

Answer: This usually happened when you were working on a regression, and it's most likely because the html output is enabled and you have a lot of html output, which run out of memory. you can disable the html output by: ods html close; or go to tools-->options-->preference-->results to uncheck html output.

Also if you pay attention to the error, you might see some output dataset has large number of observations, try to disable that dataset output and it might solve your problem.

ERROR 3(from netezza):: ERROR: CLI open cursor error: ERROR: found delim '|' in a data field, specify escapeChar '\' option in the external table definition.

Answer: In case if there are those pipe delim "|" in your records, you will have trouble loading those records; In other words, you got stuck. One solution will be exclude those records from the datasets, by using the function index(var,'|')=0 for SAS or position('|' in var)=0 for Netezza.

Another Solution will be: use the case statement to filter out the pipe '|':
case when position('|' in var)>0 then
substr(var,1,position('|' in var)-1)||','||
substr(var,position('|' in var)+1,length(var) )
else var end as var,

Related links:

Continue to next: How to get beautiful geographical picture in SAS?   SAS tutorial home
Back to: "Invisible" SAS ERROR in reading raw dataset, trick with fslist   Statistics tutorial home