LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (November 2009, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Tue, 10 Nov 2009 09:34:28 -0500
Reply-To:   Kristin Graves <gravesk@CONED.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Kristin Graves <gravesk@CONED.COM>
Subject:   Re: Limit to number of libraries with Excel engine?

Hi all -

I recently ran into the same problem as Scott. That is, after my macro created 64 libnames it stopped creating the libnames and started giving me the following error messages: ERROR: Connect: Unspecified error ERROR: Error in the LIBNAME statement.

In the original thread of emails, Mary Howard had suggested the following (untested) work-around, in which you simply clear the libname at the end of each macro loop. I just wanted to add that I tried Mary's work-around and it works!

Thanks to Mary and all others who posted in this thread.

Kristin Graves

************************************************** 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 ***************************************************

>On 9/30/08, Bucher Scott <> wrote: >> 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 >>

Back to: Top of message | Previous page | Main SAS-L page