ERROR: CLI cursor fetch error: Numeric value out of range

[HaveFunWSAS]

Dear Community,

I was using SAS EG to pull data from a Netezza database using below code:

proc sql outobs=10;

connect to odbc(dsn=Netezza_PROD uid=xxxxx password = xxxxx); * Retail Deposit;

create table test as

select * from connection to odbc(

select *

from database.dbadmin.table

);

disconnect from odbc;

quit;

I got this error "ERROR: CLI cursor fetch error: Numeric value out of range".

 I found the answer in this post is helpful https://communities.sas.com/t5/SAS-Programming/quot-CLI-cursor-extended-fetch-error-Numeric-value-ou... 

But in my case, I have lots of variables and it's hard to check which variable is the issue one.. Need your help please.

Thanks.

[SASKiwi]

SAS sets the variable value to missing for these errors. Check your output variables for missing values. AFAIK there is no shortcut to identifying the problematic variable.

[HaveFunWSAS]

Thanks for your reply.

I didn't see output, sas stopped when meet this error.

-----------------------------------------------------

37 disconnect from odbc;

NOTE: Statement not executed due to NOEXEC option.

38 quit;

NOTE: The SAS System stopped processing this step because of errors.

[SASKiwi]

It is likely you are going to have to cast columns with large numerics to character so SAS can handle them.

Run your test query selecting just one column, then add one column at a time to it until you get the error. I know this is tedious, but as I said I don't think there is a shortcut to identify the problematic columns.

[Kurt_Bremser]

Netezza should maintain DICTIONARY tables, similar to those provided by SAS. These should give you the information you need to identify the problem variables.

[Kurt_Bremser]

According to https://dwgeek.com/netezza-system-tables-views.html/, the view is called _v_sys_columns.