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

Excel Sheet/Range Gotcha

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

Excel Sheet/Range Gotcha

While attempting to read a worksheet from a workbook I couldn't read a named range.  I used LIBNAME EXCEL to open the workbook and although I could see the sheets and ranges in the workbook in Excel, this one worksheet acted like it didn't exist when viewed from SAS.

 

Took me a while to figure it out, the problem was with the naming of the sheet.  Excel has tab name length limit of 32 bytes, whereas SAS also will accept names up to 32 bytes.  

 

The problem (bug?) is that for worksheets, SAS appends a dollar sign to the end.  Apparently, unlike Windows with its short filenames and long filenames, SAS doesn't have a way to rename a 32 byte worksheet name so it can add the '$' and remain 32 bytes or less. 

 

Turns out an error message shows when you attempt to create a long-named sheet, but no error is given if you try to read one.  

 

libname exbk excel path="Test.xlsx";

/* fails */
DATA exbk.VeryLongFileName_ThirtyTwo_Bytes;
SET SASHelp.Class;
run;

/* works fine */
DATA exbk.NotSoLongFileName_26_Bytes;
SET SASHelp.Class;
run;

libname exbk clear;

 

 

Here is the log:

1
2 libname exbk excel path="Test.xlsx";
NOTE: Libref EXBK was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\Users\pchoate\Desktop\Test.xlsx
3
4 DATA exbk.VeryLongFileName_ThirtyTwo_Bytes;
5 SET SASHelp.Class;
6 run;

ERROR: You may create a table with a name up to 31 chars long in Excel.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.03 seconds


7
8 DATA exbk.NotSoLongFileName_26_Bytes;
9 SET SASHelp.Class;
10 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set EXBK.NotSoLongFileName_26_Bytes has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


11
12
13 libname exbk clear;


Accepted Solutions
Solution
‎09-19-2016 11:17 AM
Respected Advisor
Posts: 4,819

Re: Excel Sheet/Range Gotcha

That's right.

 

Sheet names actually end with a $ sign in Excel too. They are just not shown to the user.

 

When SAS creates a data table in Excel with the Excel libname engine, it creates both a named range and a sheet.

PG

View solution in original post


All Replies
Solution
‎09-19-2016 11:17 AM
Respected Advisor
Posts: 4,819

Re: Excel Sheet/Range Gotcha

That's right.

 

Sheet names actually end with a $ sign in Excel too. They are just not shown to the user.

 

When SAS creates a data table in Excel with the Excel libname engine, it creates both a named range and a sheet.

PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 332 views
  • 0 likes
  • 2 in conversation