BookmarkSubscribeRSS Feed
bsam17
Calcite | Level 5

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?

5 REPLIES 5
Reeza
Super User
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.
bsam17
Calcite | Level 5

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?

Reeza
Super User
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.
PGStats
Opal | Level 21

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
SASKiwi
PROC Star

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 5 replies
  • 1179 views
  • 0 likes
  • 4 in conversation