SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

LIbname to Excel

Reply
Occasional Contributor
Posts: 8

LIbname to Excel

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?

Grand Advisor
Posts: 16,268

Re: LIbname to Excel

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.
Occasional Contributor
Posts: 8

Re: LIbname to Excel

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?

Grand Advisor
Posts: 16,268

Re: LIbname to Excel

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 Smiley Wink

You could look at proc contents once you've connected to that sheet but I don't think it will show the long sheet.
Respected Advisor
Posts: 4,606

Re: LIbname to Excel

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

PG
Respected Advisor
Posts: 2,902

Re: LIbname to Excel

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.  

Post a Question
Discussion Stats
  • 5 replies
  • 354 views
  • 0 likes
  • 4 in conversation