XLSLIB readonly access

Reply
Occasional Contributor
Posts: 14

XLSLIB readonly access

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

       data

Thanks!

Lucas

SAS Super FREQ
Posts: 8,742

Re: XLSLIB readonly access

Hi:

  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.

cynthia

Occasional Contributor
Posts: 14

Re: XLSLIB readonly access

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);

data &sf;

    retain SITE PATID PROTSEG ERRMSG ERRCODE KEY DOC REVIEW REVIEWDT COMMENT;

    set xlslib."&var"n (sasdatefmt=(REVIEWDT='mmddyy10.') rename=(SITE=SITE1

                                            PATID=PATID1

                                            PROTSEG=PROTSEG1

                                            ERRMSG=ERRMSG1

                                            ERRCODE=ERRCODE1

                                            KEY=KEY1

                                            DOC=DOC1

                                            REVIEW=REVIEW1

                                            REVIEWDT=REVIEWDT1

                                            COMMENT=COMMENT1));

    by key1;

        *ensures all variables are formatted consistently;

        site=input(site1,$5.);

        patid=input(patid1,$13.);

        protseg=input(protseg1,$1.);

        errmsg=input(errmsg1,$200.);

        errcode=input(errcode1, 2.);

        key=input(key1, $40.);

        doc=input(doc1, $200.);

        review=input(review1, $3.);

        reviewdt=input(reviewdt1, mmddyy10.);

        comment=input(comment1, $500.);

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

run;

        %end; %mend except;

%except;

Super User
Super User
Posts: 7,392

Re: XLSLIB readonly access

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

Contributor
Posts: 42

Re: XLSLIB readonly access

Hi, have you tried to save the excel as shared one? You will then be able to read the data.

Jakub

Occasional Contributor
Posts: 14

Re: XLSLIB readonly access

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.

Super User
Super User
Posts: 7,392

Re: XLSLIB readonly access

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

Ask a Question
Discussion stats
  • 6 replies
  • 400 views
  • 3 likes
  • 4 in conversation