Excel Sheet/Range Gotcha

PChoate — Tue, 13 Sep 2016 00:09:38 GMT

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;

Re: Excel Sheet/Range Gotcha

PGStats — Tue, 13 Sep 2016 03:36:31 GMT

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.

topic Re: Excel Sheet/Range Gotcha in Microsoft Integration with SAS

Excel Sheet/Range Gotcha

Re: Excel Sheet/Range Gotcha