topic SAS 9.3 Libname for Excel file missing one sheet in SAS Procedures

SAS 9.3 Libname for Excel file missing one sheet

bsam17 — Tue, 21 Jul 2015 16:10:29 GMT

Hi,

I am tryin to import an Excel file that has three sheets, but when I try to use a libname statement to access the sheet names, it only seems to pick up 2 sheets. Does anyone have experience with this and can tell me what the issue might be?

The code I'm using is:

libname xl "C:\....\Sample File.xls";

Thanks a lot

Re: SAS 9.3 Libname for Excel file missing one sheet

ballardw — Tue, 21 Jul 2015 16:24:00 GMT

Please show the names of the sheets in the Excel file.

Re: SAS 9.3 Libname for Excel file missing one sheet

bsam17 — Tue, 21 Jul 2015 21:41:35 GMT

For example, the sheet names are:

1) Cash and Liquidity Cushion Mgt

2) Cash Movement Summary

3) Fees & Misc.

I have a few hundred Excel files with varying numbers of sheets, but the first sheet ("Cash and Liquidity Cushion Mgt") is never identified. I tried renaming or re-ordering but to no avail.

Re: SAS 9.3 Libname for Excel file missing one sheet

Reeza — Tue, 21 Jul 2015 21:46:15 GMT

Then it's something in the sheet.

How does this one differ from the others?

Copy the workbook and delete sections of the sheet at time (binary search) until you narrow it down.

Re: SAS 9.3 Libname for Excel file missing one sheet

PGStats — Tue, 21 Jul 2015 22:18:29 GMT

The sheet name is too long. Try making them shorter.

Re: SAS 9.3 Libname for Excel file missing one sheet

Reeza — Tue, 21 Jul 2015 22:20:11 GMT

It's only 30 chars?

Re: SAS 9.3 Libname for Excel file missing one sheet

ballardw — Tue, 21 Jul 2015 22:43:48 GMT

Excel sheets can have blanks that aren't seen unless you highlight the name like preparing to rename. And even then depending on the font may not be noticeable. I was suspecting long names which is why I asked earlier.

Re: SAS 9.3 Libname for Excel file missing one sheet

PGStats — Wed, 22 Jul 2015 01:23:48 GMT

Actually you can still read table xl.'Cash and Liquidity Cushion Mgt$'n but you can't see it in SAS Explorer window.