How to check names of excel sheets in SAS

[Ayisha]

How to check names of excel sheets in order?

I used proc datasets lib= option 

but the sheet names appear in alphabetical order.

How to get the names in the same order as they appear in excel sheet?

[Patrick]

I believe this information doesn't get read into SAS. Given that you could also read ranges instead of sheets and that there could be more than one range per sheet (=multiple SAS tables reading data from the same sheet) I also don't see how SAS could reasonably store such information.

If you really need the order of the sheets within the Excel then I guess you would need some VB script (or similar) which extracts the information and then read this into a separate SAS table. What's possible depends on the OS of your SAS server and if you can issue OS level commands (=option XCMD must be set).

[Ksharp]

libname x xlsx 'c:\temp\x.xlsx';

proc sql;

select *

 from dictionary.tables

  where libname='X' ;

quit;

[Tom]

Just read the information from the XLSX file yourself.  An XSLX file is just a ZIP file that contains a lot of XML files. The lists of sheet names is in the XML file named xl/workbook.xml.

Note that you will need to copy the file out of the workbook and into a new physical file for the XMLV2 engine to read from it.

I made a little workbook with three sheets and then moved the order of them around and saved it to disk. Then I ran this code to pull out the list of sheets.

*----------------------------------------------------------------------;
* Copy xl/workbook.xml from XLSX file to physical file ;
*----------------------------------------------------------------------;
filename _wbzip ZIP '3sheets.xlsx' member='xl/workbook.xml';
filename _wb temp ;
data _null_;
rc=fcopy('_wbzip','_wb');
run;

*----------------------------------------------------------------------;
* Generate LIBNAME pointing to copy of xl/workbook.xml from XLSX file ;
*----------------------------------------------------------------------;
filename _wbmap temp;
libname _wb xmlv2 xmlmap=_wbmap automap=reuse;

data sheet_names;
 set _wb.sheet;
run;
proc print width=min;
run;

       sheets_     sheet_                   sheet_
Obs    ORDINAL    ORDINAL    sheet_name    sheetId    sheet_id

 1        1          1       Was_Sheet3       3         rId1
 2        1          2       Was_Sheet1       1         rId2
 3        1          3       Was_Sheet2       2         rId3

The variable SHEET_ORDINAL that the XMLV2 engine generated will have the order of the sheets.

[shreyvirmani]

Thanks for this