DATA Step, Macro, Functions and more

%SQZ_LIBRARY Macro

Reply
Contributor
Posts: 36

%SQZ_LIBRARY Macro

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.

Respected Advisor
Posts: 4,173

Re: %SQZ_LIBRARY Macro

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

Contributor
Posts: 36

Re: %SQZ_LIBRARY Macro

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.

Respected Advisor
Posts: 4,173

Re: %SQZ_LIBRARY Macro

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?

Occasional Contributor
Posts: 9

Re: %SQZ_LIBRARY Macro

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

Ask a Question
Discussion stats
  • 4 replies
  • 401 views
  • 2 likes
  • 3 in conversation