Hi All,
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
from emprd.serviceauth;
quit;
%put &count;
%macro test(finish);
%let i=1;
%let obs = 1;
%let obs_1 = %eval(&obs+1500);
%do %while(&i < %eval(&finish/1500)+1);
proc sql ;
create table final_&i as
select diagnosisnarrative
from emprd.serviceauth(firstobs = &obs obs = &obs_1)
where id in (select id from final_6);
quit;
data final;
set final final_&i;
run;
%let i=%eval(&i+1);
%let obs = %eval(&obs+1500);
%let obs_1 = %eval(&obs_1 + 1500);
%end;
%mend test;
%test(&count)
I came across another method which is to use a macro, explained in the below paper :
http://www.lexjansen.com/wuss/2011/posters/Papers_Bettinger_R_74821.pdf
%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.
Thank you,
Harish.
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.
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm
Hi patrick,
I think i need to edit the odbc.ini file(UNIX platform) in this case if i have to set readbuff option to 1500. is that right.
Thank you,
Harish.
According to the docu it's a simple lib name option (see link in above post).
On a side note: why aren't you marking your thread as a question?
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.!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.