how to display the sheet in actual order ?

[tianerhu]

I have a xlsx file , and there are 3 sheet in the xlsx file , the first sheet is SheetA ,the second is SheetBB, the third is SheetC. like this ：

libname apple  xlsx 'C:\practice everyday\input\input01.xlsx';
proc contents data = apple._all_;
run;

the output :

how to display the sheets in actual order that they are in xlsx file?

[Tom · Accepted solution]

If you want to discover the sheet order read the XML file out of the XSLX file (an XLSX file is just a ZIP file) that has that information.

%let filename='c:\downloads\three.xlsx';
%let sheets=worksheets;

*----------------------------------------------------------------------;
* Generate XMLMAP to read the sheetnames from xl/workbook.xml ;
*----------------------------------------------------------------------;
filename _wbmap temp;
data _null_;
  file _wbmap;
  put '<SXLEMAP version="2.1"><TABLE name="Sheets">'
    / '<TABLE-PATH>/workbook/sheets/sheet</TABLE-PATH>'
    / '<COLUMN name="Sheet"><TYPE>character</TYPE>'
    / '<DESCRIPTION>Sheet Name</DESCRIPTION>'
    / '<PATH>/workbook/sheets/sheet/@name</PATH>'
    / '<DATATYPE>string</DATATYPE><LENGTH>32</LENGTH>'
    / '</COLUMN>'
    / '<COLUMN name="State"><TYPE>character</TYPE>'
    / '<DESCRIPTION>Sheet State</DESCRIPTION>'
    / '<PATH>/workbook/sheets/sheet/@state</PATH>'
    / '<DATATYPE>string</DATATYPE><LENGTH>20</LENGTH>'
    / '</COLUMN>'
    / '</TABLE></SXLEMAP>'
  ;
run;

*----------------------------------------------------------------------;
* Copy xl/workbook.xml from XLSX file to physical file ;
%* Note: Cannot use ZIP filename engine with XMLV2 libname engine ;
/* filename _wb pipe %sysfunc(quote(unzip -p &filename xl/workbook.xml)); */
*----------------------------------------------------------------------;
filename _wbzip ZIP &filename member='xl/workbook.xml';
filename _wb temp ;
data _null_;
  infile _wbzip recfm=f lrecl=30000;
  file _wb recfm=f lrecl=30000;
  input;
  put _infile_;
run;

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

*----------------------------------------------------------------------;
* Read sheet names from XLSX file into a SAS dataset. ;
* Create valid SAS dataset name from sheetname or sheetnumber. ;
*----------------------------------------------------------------------;
data &sheets ;
  length Number 8;
  set _wb.sheets end=eof;
  number+1;
  length Memname $51 Filename $256 ;
  label number='Sheet Number' memname='Mapped SAS Memname' filename='Source Filename' ;
  memname = nliteral(sheet) ;
  filename = &filename ;
run;

proc print;
run;

Obs    Number    Sheet     State    Memname           Filename

 1        1      Sheet2             Sheet2     c:\downloads\three.xlsx
 2        2      Sheet1             Sheet1     c:\downloads\three.xlsx
 3        3      Sheet3             Sheet3     c:\downloads\three.xlsx

[tianerhu]

Thank you for your help.