topic Re: Importing excel file using libname statement in SAS Programming

Importing excel file using libname statement

Anahit — Fri, 20 Oct 2017 21:27:31 GMT

I wanted to import sheet from excel (xlsx, SAS 9.4) named "TS" using libname statement, but I got such message:

No cell rectangle in range
Requested Input File Is Invalid
ERROR: File EXCELLIB.TS.DATA does not exist.

When I use proc import, it works. Also it works when I am changing the sheet name like "TSO". I have other sheets named AE/IE etc. for which libname works without any problem. Could you please help me to find where from the issue can come (it is from code, from excel etc. )

Re: Importing excel file using libname statement

spalm — Sat, 21 Oct 2017 00:51:35 GMT

Can you share an example of the libname statement that is generating the error?

Re: Importing excel file using libname statement

Anahit — Sat, 21 Oct 2017 14:43:14 GMT

Sure, please:

LIBNAME gx xlsx ".D:\Desktop\Anahit\XX.xlsx" ;

data df;
set gx.TS;
run;

Just want to mention that this code work perfectly with other sheets of the same file.

Re: Importing excel file using libname statement

art297 — Sat, 21 Oct 2017 15:13:12 GMT

Submit the problem to SAS technical support. I was able to replicate the problem using a simple workbook with three worksheets named TSO, TS and SO. The three worksheets were identical with only two variables x and y named on the first row, followed by values on the 2nd and 3rd rows. Worked perfectly for TSO and SO, but received the same error message you got when trying to read the TS worksheet.

Art, CEO, AnalystFinder.com

Re: Importing excel file using libname statement

art297 — Sat, 21 Oct 2017 16:08:36 GMT

Unfortunately, the problem appears to be intermittent. The first time I ran the code I got the exact same error message as you did.

I had run it on SAS UE using VMWare on Chrome.

Interestingly, I got the error message, the log showed that the other two files had imported correctly, but the output menu didn't appear, so I wasn't able to look at the files.

Since then, I've been unable to replicate the problem. I even cold booted my system, but now everything imports correctly.

In short, this one is confusing! I'd still raise it with Technical Support as our both getting the same error message, to me, is enough evidence that something is wrong.

Art, CEO, AnalystFinder.com

Re: Importing excel file using libname statement

Anahit — Thu, 26 Oct 2017 15:13:39 GMT

Hello Art,

I found the solution. I removed all hidden names from excel using visual basic and it worked. So problem was related to excel,not SAS. Thank you.

Re: Importing excel file using libname statement

art297 — Thu, 26 Oct 2017 15:36:01 GMT

Glad you found a solution but, on the other hand, I'm not sure what the problem is/was.

When I first ran your code on a workbook that I created (which didn't have any hidden names), I got the same error as you did.

However, that only happened the first time and, since then (importing the same workbook), I haven't been able to replicate the problem. The workbook keeps importing correctly ,, even though I haven't changed it.

Art, CEO, AnalystFinder.com

Re: Importing excel file using libname statement

Anahit — Thu, 26 Oct 2017 16:28:14 GMT

Before finding the solution (in my case) , I had written to SAS Technical Support and here their answer:

Hello Anahit,

When I attempt to replicate this with on the file provided it works as expected:

4 libname xl xlsx "C:\testing\7612261489\data.xlsx";

NOTE: Libref XL was successfully assigned as follows:

Engine: XLSX

Physical Name: C:\testing\7612261489\data.xlsx

5 data newx;

6 set xl.TS;

7 run;

NOTE: The import data set has 26 observations and 10 variables.

NOTE: There were 26 observations read from the data set XL.TS.

NOTE: The data set WORK.NEWX has 26 observations and 10 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

I suspect two possible issues. First, Excel can sometimes have issues with import/export when sheet names use only two or three characters. This is usually not an issue for the XLSX engine you are using. However the core issue is rooted in how ranges, sheet names, and cell references are stored and accessed. It is possible for a range to become mismatched from its sheet or cell(s) and thus cause an error such as this. Second possible issue would be some minor corruption in the original Excel file. When the file is modified or recreated the corruption is corrected and thus the error is resolved. Unfortunately neither case is something we can diagnosis from withing SAS.

Now I do have one known issue where this error is reported but it has not been reported in your specific situation. This error is noted in SAS Note 60543 http://support.sas.com/kb/60543 and is fixed in the hot fix referenced in that note. It would not be a bad idea to apply that hot fix to your installation.