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

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

Reply
Contributor
Posts: 70

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

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

Super Contributor
Posts: 578

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

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;

Super User
Posts: 5,424

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

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

Data never sleeps
Contributor
Posts: 70

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

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

Contributor
Posts: 70

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

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.

Super User
Posts: 5,424

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

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

Data never sleeps
Super User
Posts: 7,762

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

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Employee
Posts: 33

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

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.

Ask a Question
Discussion stats
  • 7 replies
  • 1817 views
  • 3 likes
  • 5 in conversation