Re: Limit to number of libraries with Excel engine?

Maybe instead of using the call execute, load up a dataset with your library
names but also include a obsnum variable, then go get the obsnum variable,
assign the library, then ***clear** the library on each loop. Not
tested....

%macro fileout ;
proc sql noprint;
select count(obsnum) into :max from libnames;
quit;
%do i=1 %to &max;
proc sql noprint;
select libname into &libname
where obsnum=&i;
quit;
%put Processing libname=&libname;
libname .....&libname;
data libname.setname;
...
run;
libname &libname clear;
%end;
%mend fileout ;


data libnames;
infile 'libnamelist.txt' truncover ;
input libname $50. ;
obsnum + 1;
run ;

%fileout ;

-Mary
----- Original Message -----
From: Bucher Scott
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, September 30, 2008 2:12 PM
Subject: Limit to number of libraries with Excel engine?


H,

This is related to the problem I originally posted in "Use of Call
Execute in Proc SQL". I am trying to create 1,000 multisheet Excel
workbooks. All the data that must be exported to these workbooks is
contained in a single SAS data set. I attempted to create the 1,000
libraries (using the Excel engine), so I can subsequently use the hash
method of splitting a file into multiple data sets. I thought this would
be preferable to how I usually do things, which would be by splitting
the data set into 1,000 different data sets and then exporting them in
1,000 different data steps.

However, when I try to create 1,000 libraries through the following
method, only 64 are created. The remaining observations have a return
code of 7280001, i.e. "ERROR: Connect: Unspecified error".

data b;
set a;
rc = libname(cats('_', id)
, cats("&folder_output\", id, ".xls"));
msg = sysmsg();
run;

I have not been able to turn up any information on the specific return
code or limits to the number of libraries that can simultaneously be
opened. Evidently there is limit of 64 libraries using the Excel engine
within a windows environments? Are there any options to get around this
limit? Is there any more elegant method to create the workbooks aside
from creating 64 libraries at a time, exporting the data, clearing the
libraries, then creating new libraries? I attempted to create and delete
the libraries one at a time within the same data step containing the
hash table, but it seems the libraries are not created until the data
step is finished executing.

Thanks,
Scott