BookmarkSubscribeRSS Feed
hari1109
Fluorite | Level 6

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.

4 REPLIES 4
Patrick
Opal | Level 21

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

hari1109
Fluorite | Level 6

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.

Patrick
Opal | Level 21

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?

JerryH
Calcite | Level 5

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.!

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1277 views
  • 2 likes
  • 3 in conversation