BookmarkSubscribeRSS Feed
jdmarshg
Obsidian | Level 7

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

7 REPLIES 7
DBailey
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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

Data never sleeps
jdmarshg
Obsidian | Level 7

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
Obsidian | Level 7

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
Tourmaline | Level 20

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

Data never sleeps
Kurt_Bremser
Super User

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
SAS Employee

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 5325 views
  • 3 likes
  • 5 in conversation