08-29-2012 05:51 AM
i have been trying pull information from a table. If i limit the no of rows to 1500 then i am able to pull the data successfully buy i have to apply a filter condition on the database table. The below is the error i am receiving while i attempt the get the records from the database table. I am using Unix SAS and am pulling the information from SQLSVR database table.
The Error I am receiving : ERROR: CLI cursor extended fetch error: Unable to retrieve error message.
The below is one of the methods i used to pull the information as i am not able to pull more than 1500 records at a time.
proc sql ;
select count(*) into : count
%let obs = 1;
%let obs_1 = %eval(&obs+1500);
%do %while(&i < %eval(&finish/1500)+1);
proc sql ;
create table final_&i as
from emprd.serviceauth(firstobs = &obs obs = &obs_1)
where id in (select id from final_6);
set final final_&i;
%let obs = %eval(&obs+1500);
%let obs_1 = %eval(&obs_1 + 1500);
I came across another method which is to use a macro, explained in the below paper :
%SQZ_LIBRARY( LIBNAME /* name of SAS library to squeeze */
, EXCLUDE= /* List of datasets to EXclude */
, INCLUDE= /* List of datasets to INclude */
, LIST=N /* Flag for listing of dataset contents */
, TRIALRUN=Y /* Flag for mode of operation */
There is no macro code available like the one we have for %Squeeze macro.
Can any one explain how to use this macro to squeeze my SQLSVR database table emprd.serviceauth.
08-29-2012 06:35 AM
Before spending time with work-arounds you could also try to solve the problem.
It's SAS/Access to ODBC you're using - right? If you can read 1500 rows then try and set readbuff to this value. The error could be caused by a simple out of memory condition.
09-10-2012 04:46 PM
One of the basics of program efficiency is how long it takes the system to compile the code. SAS is no exception - that is why the best code is the simplest code from an efficiency standpoint. Patrick's solution is, of course, the simplest and the most efficient. We SAS programmers don't pride ourselves so much on the complexity and volume of our code as we pride ourselves on how fast the code processes. Also, I have found by bitter experience that the more complex I try to make my code (e.g. do as much as possible in one step) the more probable I will make an error. Just a word of caution.!