01-29-2015 07:55 AM
I have an Excel spreadsheet with multiple worksheets that I am reading into SAS using the XLSLIB libref. I am converting each worksheet into SAS format via a combination of proc sql and macro code. The problem is the program will fail if someone has the Excel file open on their machine. I only need to read from the file, not write to it. Is there an option I can include in my XLSLIB statement which will access the file as read-only? I tried 'access=readonly' but I still get the following error:
ERROR: Connect: The Microsoft Office Access database engine cannot open or write to the file ''. It
is already opened exclusively by another user, or you need permission to view and write its
01-29-2015 08:07 AM
I don't know what you mean by the "XLSLIB" statement -- do you mean a LIBNAME statement? Or are you using PROC IMPORT? Can you show ALL the code you've tried, not just the ERROR message.
01-29-2015 08:11 AM
Here is what I have:
libname XLSLIB "filepath\filename.xls" access=readonly mixed=yes stringdates=yes;
proc sql; create table a as select * from dictionary.tables where libname="XLSLIB" ; quit;
proc sql; select memname into :snamlist separated by '*' from a ; quit;
proc sql; select count(memname) into :n from a ; quit;
%put &snamlist; %put &n;
%macro except; %do i=1 %to &n; %let var=%scan(&snamlist,&i,*); %let sf=%substr(&var,1,%length(&var)-1);
retain SITE PATID PROTSEG ERRMSG ERRCODE KEY DOC REVIEW REVIEWDT COMMENT;
set xlslib."&var"n (sasdatefmt=(REVIEWDT='mmddyy10.') rename=(SITE=SITE1
*ensures all variables are formatted consistently;
drop site1 patid1 protseg1 errmsg1 errcode1 key1 doc1 review1 reviewdt1 comment1;
format site $5. patid $13. protseg $1. errmsg doc $200. comment $500. errcode 2. review $3. reviewdt mmddyy10. key $40.;
%end; %mend except;
01-29-2015 08:30 AM
libname to Excel will create a lock on the file. AS far as I am aware there is not method to open a document as a libname without keeping open as by its very nature requires access to the file. For instance, you goto look at one of the sheets, and someone else has deleted the file or moved the sheet, then your whole libname becomes corrupt. So yes, it wants exclusive access to the file whilst the libname is in place or whilst someone else has it open. Sure there is shared workbooks, but that only works in Office technology.
My suggestion, avoid Excel like the plague. Save your data to delimited text files, e.g. csv, then write a datastep to read in the csv and create a datastep. You will find that this way you have no "Excel" related issues (of which there are plenty), you will have full control over how the data is imported/output to dataset, plus there is no locking issue. Plus as a bonus, you data is portable across systems, open source so anything can read it.
Edit, to avoid the next question, its pretty simple to create a small VBA macro to save all the sheets out to CSV format: vba - Saving excel worksheet to CSV files with filename+worksheet name using VB - Stack Overflow
01-29-2015 09:37 AM
This works and serves my purpose. I know Excel can be particularly finicky when used in combo with SAS; however, this is my only option for now as I am using Excel to maintain a database which is used to facilitate dynamic reports in the rest of my SAS program.
01-29-2015 09:47 AM
Well, you have identified your issue there then: "I am using Excel to maintain a database which is used to facilitate dynamic reports".
I will replace my car engine with a hairdryer and kettle, then wonder why it doesn't work. What you want is: "I am using a database, which feeds into a custom reporting tool."