12-13-2014 11:36 PM
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.
12-14-2014 10:36 PM
You could use a passthrough...
connect using LipSDD00 as cLIP;
from connection to cLIP (
select count_big(*) from Fundamental
12-15-2014 06:47 AM
You might need to use CAST() in the pass thru expression, otherwise chances are that you still encounter the same limitation.
12-15-2014 10:34 AM
12-15-2014 11:28 AM
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.
12-15-2014 06:42 PM
If this is DI Studio generated code open a track to SAS tech support and demand a fix!
12-15-2014 02:40 AM
Assuming you have a libname for the SQL Server table, you could use this brute force approach as a last-ditch solution:
set sqllib.fundamental end=finish;
if finish then call symput('sourcecnt',put(_n_,best20.));
12-17-2014 02:02 PM
See my reply to the other thread you mentioned above for more details on this macro:
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.