topic LIbname to Excel in Microsoft Integration with SAS

LIbname to Excel

bsam17 — Tue, 22 Dec 2015 16:27:29 GMT

I am using a libname statement to access the different sheets in an .xls workbook. For example,:

libname xls "C:\MyDoc.xls";

However, I noticed that sometimes a sheet with a long sheet name does not appear in my library. Is there a workaround for this apparent character limit?

Re: LIbname to Excel

Reeza — Tue, 22 Dec 2015 16:31:20 GMT

32 characters is the limit. I'm not sure there's a work around for Excel files, you could try an ODBC connection direct pass through method.

Re: LIbname to Excel

bsam17 — Tue, 22 Dec 2015 16:36:44 GMT

Thanks. Can you illustrate how to do that? Alternatively, is there a way to count the number of sheets in an Excel workbook without extracting the name, even if the sheet name is longer than 32 characters?

Re: LIbname to Excel

Reeza — Tue, 22 Dec 2015 17:02:53 GMT

There's an example here:
https://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002644817.htm

From a brief google and test, it looks like Excel 2010, at least, limits the characters to 31 chars. The general consensus seems to be live within that otherwise you run into issues in other applications 😉

You could look at proc contents once you've connected to that sheet but I don't think it will show the long sheet.

Re: LIbname to Excel

PGStats — Tue, 22 Dec 2015 17:19:33 GMT

I was confronted with that issue once with an older xls file and ended up (reluctantly) editing the file to shorten the sheet name.

Re: LIbname to Excel

SASKiwi — Tue, 22 Dec 2015 18:50:49 GMT

There is no workaround in the current SAS version for displaying sheet/table names longer than 32 characters in SAS library lists. The same problem occurs with accessing databases such as Oracle, SQL Server etc.

The only way to read these is using SQL PASSTHRU where you can specify a sheet name longer than 32 characters.

This is an often-requested enhancement to SAS so I'm hopeful it will happen in a future release.