topic How to check names of excel sheets in SAS in SAS Studio

How to check names of excel sheets in SAS

Ayisha — Tue, 08 Oct 2019 20:48:45 GMT

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?

Re: How to check names of excel sheets in SAS

Patrick — Tue, 08 Oct 2019 23:36:53 GMT

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).

Re: How to check names of excel sheets in SAS

Ksharp — Wed, 09 Oct 2019 12:39:11 GMT

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

proc sql;

select *

from dictionary.tables

where libname='X' ;

quit;

Re: How to check names of excel sheets in SAS

Tom — Wed, 09 Oct 2019 13:35:13 GMT

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.

Re: How to check names of excel sheets in SAS

shreyvirmani — Thu, 01 Oct 2020 03:20:17 GMT

Thanks for this