topic Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL in SAS Data Management

Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

jdmarshg — Sun, 14 Dec 2014 04:36:47 GMT

Good evening,

I'm receiving the following error and wanted to know how I would handle in SAS.

For SQL Server I would have to use SELECT COUNT_BIG(*) is there an equivalent?

select count(*) into :sourcecnt from LipSDD00.Fundamental;

CLI open cursor error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic

overflow error converting expression to data type int.

Regards,

Jonathan

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

DBailey — Mon, 15 Dec 2014 03:36:47 GMT

You could use a passthrough...

proc sql;

connect using LipSDD00 as cLIP;

select

recs

into

:sourcecnt

from connection to cLIP (

select count_big(*) from Fundamental

);

quit;

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

Kurt_Bremser — Mon, 15 Dec 2014 07:40:21 GMT

Assuming you have a libname for the SQL Server table, you could use this brute force approach as a last-ditch solution:

data _null_;

set sqllib.fundamental end=finish;

if finish then call symput('sourcecnt',put(_n_,best20.));

run;

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

LinusH — Mon, 15 Dec 2014 11:47:41 GMT

You might need to use CAST() in the pass thru expression, otherwise chances are that you still encounter the same limitation.

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

jdmarshg — Mon, 15 Dec 2014 15:34:59 GMT

Thank you for all the options for a solution.

It seems from this other thread I found that SAS DIS that the %macro etls_recordCheck; which is automatically generating these counts.

Here is the other thread.

I am continuing to research this issue and figure out a work around.

Regards,

Jonathan

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

jdmarshg — Mon, 15 Dec 2014 16:28:38 GMT

I would like SAS just to ignore these errors and continue on if I could for a quick work around until another solution is found.

Any suggestions ?? How do I go about the SAS System to continue processing?

The SAS System stopped processing this step because of errors.

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

LinusH — Mon, 15 Dec 2014 23:42:36 GMT

If this is DI Studio generated code open a track to SAS tech support and demand a fix!

Re: Arithmetic overflow error converting expression to data type int. (Tracking Counts) Proc SQL

MikeFrost — Wed, 17 Dec 2014 19:02:24 GMT

Hi jdmarshg,

See my reply to the other thread you mentioned above for more details on this macro:

Re: D.I. Studio - etls_recordCheck macro issues

You can disable this macro as I note in this thread or change the code it generates to suit your needs. SELECT COUNT_BIG(*) seems like Transact-SQL specific to SQL Server 2008 and higher, and would only be needed for tables with more than 2,147,483,647 rows - not unheard of, but certainly uncommon.

Mike F.