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

[jdmarshg]

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

[DBailey]

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;

[LinusH]

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

[jdmarshg]

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

[jdmarshg]

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.

[LinusH]

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

[Kurt_Bremser]

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;

[MikeFrost]

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.