PROC Import "hiddensheet$" reference

[dbjosiah]

Hi, I downloaded a large Excel file (over 5000 records). When I run PROC Import (which I've used several times before in other projects), the "which table do you want to import?" screen only lists "hiddenSheet$" (see picture). There's nothing else in the dropdown list, and Options doesn't have anything of relevance. Running the import does not give an error, but yields a file with 67 records and related but wrong variables.

Looking for a hidden worksheet in Excel (Home/format cells/hide/unhide) doesn't show anything. 

I finally managed to get the correct import by value-pasting the data table into a new file. So my question is, why is SAS seeing something I can't find, and how can I get to it? This may be an Excel question rather than a SAS one, but I thought maybe someone here might have run into this before.

I've attached my SAS code but not a sample dataset as the phantom worksheet shows confidential data (which I can't hide since I can't access that worksheet...).

Thanks!

[ballardw]

The log might have some details. Maybe.

Without an actual Excel file, which I for one won't actually download, it is hard to tell specific reasons.

When you use Proc Import then SAS is calling, at heart, an API from Microsoft as I understand things. So the question could relate to one or more features of Excel.

You find a large number of posts related to Proc Import and Excel and how that combination is marginal at best and dangerous, as you have found out, at worst for knowing what happens. I typically convert anywhere from 15 to 50 Excel sheets to CSV and read those every week because the results of Import are so unpredictable. Plus my own data step code means I know exactly what every variable name will be (as long as the column order doesn't change), what type and informat/format needed. And when the sources do change the order of columns I change the order of the input statement and that is all that is needed to read that version of the file.

[Tom]

Is the file an XLSX file?  An XLSX file is just a zip file with a series of XML files inside of it.  The file named xl/workbook.xml file lists the sheets in the workbook and there is an attribute that can mark a sheet as hidden.

For example I made a simple XLSX file with three sheets and hide the second one.

filename x zip "c:\downloads\hidden.xlsx" ;
filename workbook temp;
data _null_;
 infile x('xl/workbook.xml');
 file workbook;
 input;
 put _infile_;
run;
filename xmlmap temp;
libname workbook xmlv2 automap=reuse xmlmap=xmlmap;
proc print data=workbook.sheet; run;

       sheets_     sheet_    sheet_     sheet_                sheet_
Obs    ORDINAL    ORDINAL     name     sheetId    sheet_id    state

 1        1          1       Sheet1       1         rId1
 2        1          2       Sheet2       2         rId2      hidden
 3        1          3       Sheet3       3         rId3

[dbjosiah · Accepted solution]

Well wasn't that interesting (your code worked)! I don't quite understand your reference to macros (since View Macros (in Excel) doesn't show any), but I looked up "how to make excel show very hidden sheets," and it gave me the VBA directions. The sheet turned out to be a table of all the drop-down codes. Who knew? Thanks Tom.

[Tom]

So if you were are able to read the xl/workbook.xml file what value does it show for the SHEET_NAME variable?  Does the name look like a valid SAS dataset name (only letters, digits and underscores and does not start with a digit)?

I am able to read the hidden sheet from my example file using XLSX engine.

2604  libname x xlsx "c:\downloads\hidden.xlsx" ;
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: c:\downloads\hidden.xlsx
2605  data want;
2606    set x.sheet2;
2607  run;

NOTE: The import data set has 1 observations and 1 variables.
NOTE: There were 1 observations read from the data set X.sheet2.
NOTE: The data set WORK.WANT has 1 observations and 1 variables.

[dbjosiah]

SAS just called it "hiddenSheet" with sheet_state=veryHidden. Excel called it "hiddenDataSheet (hiddenSheet)" in the VBA Project Explorer window and all I had to do was toggle its Visible field.

The data are exported from a fairly complicated database, so it makes sense that the dropdown codes would be connected somehow. As you can probably guess, that level of programming is way beyond me, but I now know what I needed to, so all is good. Thanks again!