topic Re: %SQZ_LIBRARY Macro in SAS Programming

%SQZ_LIBRARY Macro

hari1109 — Wed, 29 Aug 2012 09:51:36 GMT

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.

Re: %SQZ_LIBRARY Macro

Patrick — Wed, 29 Aug 2012 10:35:52 GMT

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

Re: %SQZ_LIBRARY Macro

hari1109 — Wed, 29 Aug 2012 11:26:54 GMT

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.

Re: %SQZ_LIBRARY Macro

Patrick — Wed, 29 Aug 2012 23:13:37 GMT

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?

Re: %SQZ_LIBRARY Macro

JerryH — Mon, 10 Sep 2012 20:46:29 GMT

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