BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tianerhu
Pyrite | Level 9

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 :

tianerhu_0-1623501077019.png

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

the output :

tianerhu_1-1623501175792.png

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

image.png

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

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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;

image.png

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
Pyrite | Level 9

Thank you for your help.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 387 views
  • 0 likes
  • 2 in conversation