Excel engine - library limitations

lbarwick — Fri, 26 Oct 2018 19:00:25 GMT

Hi,

Does anyone know if there is a limitation to the number of Excel libraries that can be created in a single session via the excel engine?

My code is below. I am using a macro with Excel file locations on my company's network to read in each individual sheet within each Excel file. After the 64th Excel file, SAS fails to read subsequent files.


%macro compile (reqnum,tab,sheet,dir);

libname &reqnum excel "&dir" mixed=yes;
data &reqnum.&sheet;
	set &reqnum.."&tab"n;

run;

%mend compile;

data _null_;
set cwrequests;
call
execute('%compile('||reqnum||','||tab||','||sheet||','||dir||')');
run;

I have checked the R349 Excel file/directory that the ERROR references and even excluded that record from my call table to see if it was an issue with that specific Excel file. No matter what I do, the program cuts out after the 64th Excel file...

Errors:

ERROR: Connect: Unspecified error
ERROR: Error in the LIBNAME statement.
318 +
data R349group1; set R349."group1$"n; drop group; REQNUM="R349"; GROUP1="group1"; if patid>0
then patid_char = strip(put(patid,15.)); else
ERROR: Libname R349 is not assigned.
319 + patid_char=patid; drop patid; rename patid_char=PATID group1=GROUP; keep reqnum site
patid_char group1 patient_id participant_id subject_id; run;

Re: Excel engine - library limitations

Reeza — Fri, 26 Oct 2018 19:19:29 GMT

Try disconnecting from the library when you're done?
This will de-assign the lib reference.

libname &reqnum;

Re: Excel engine - library limitations

lbarwick — Fri, 26 Oct 2018 19:27:53 GMT

Ah - so simple. Thank you, it worked!

topic Re: Excel engine - library limitations in SAS Programming

Excel engine - library limitations

Re: Excel engine - library limitations

Re: Excel engine - library limitations