How to import second sheet from Excel workbook in to SAS without mentioning Sheet name

[Padma_stat]

I have Excel workbook with multiple sheets , I need to read second sheet in to SAS without mentioning sheet name. Why because I don't know what is the exact sheet name at second sheet in the excel workbook.
I have tried two methods
1. Proc impot method ( it need sheet name must)
2. Libname method (it reads all sheets from Excel workbook)

Can any suggest to import only second sheet from Excel workbook.

Thanks in advance..

[ketpt42]

With your libname connection, you should be able to find the 2nd sheet name using the sashelp.vtable dictionary view.

[Kurt_Bremser]

---

@ketpt42 wrote:
With your libname connection, you should be able to find the 2nd sheet name using the sashelp.vtable dictionary view.

---

SASHELP.VTABLE (and DICTIONARY.TABLES) does not contain a sequence number for the physical order of datasets in a library, you always get the datasets ordered alphabetically (collating order of your locale).

Extracting the order out of the XML (as @Tom suggested) seems to be the only way to do what the OP wants.

[ketpt42]

@Tom already mentioned that in his reply.

[Tom · Accepted solution]

You can find the NAMES of the sheets using the libname engine, but not their order.

You could read the XML from the XLSX file and get the names and the order from that.

%let filename='c:\downloads\order.xlsx';
%let sheets=sheet_names;

*----------------------------------------------------------------------;
* 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;

*----------------------------------------------------------------------;
* Generate LIBNAME pointing to xl/workbook.xml inside the XLSX file ;
%* Note: Cannot use ZIP filename engine with XMLV2 libname engine ;
*----------------------------------------------------------------------;
filename _wb temp;
data _null_ ;
  infile &filename zip member='xl/workbook.xml';
  file _wb;
  input;
  put _infile_;
run;
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 ;
  if eof then call symputx('nsheets',_n_-1);
  length Number 8;
  set _wb.sheets end=eof;
  number+1;
  length Memname $32 Filename $256 ;
  label number='Sheet Number' memname='Mapped SAS Memname' filename='Source Filename' ;
  filename = &filename ;
  if ^nvalid(compress(sheet),'v7') then memname = cats('Sheet',number);
  else memname = translate(trim(compbl(sheet)),'_',' ');
run;

*----------------------------------------------------------------------;
* Clear the libname and filenames used in reading the sheetnames. ;
*----------------------------------------------------------------------;
libname _wb clear ;
filename _wb clear ;
filename _wbmap clear ;

proc print data=&sheets;
run;

 Result for example where I renamed Sheet2 to Was2 and moved it before Sheet1.

Obs    Number    Sheet     State    Memname           Filename

 1        1      Was2               Was2       c:\downloads\order.xlsx
 2        2      Sheet1             Sheet1     c:\downloads\order.xlsx
 3        3      Sheet3             Sheet3     c:\downloads\order.xlsx

[Padma_stat]

its not working out for .xls files

[Tom]

---

@Padma_stat wrote:

its not working out for .xls files

---

Of course not. An XLS file uses a totally different format than an XLSX file.

[Padma_stat]

which option i need to change for reading .xls files

[Tom]

Don't start new questions on old threads. Open a new question and describe what your issue is with your XLS file.  (Is there anyway you could just convert the XLS file to an XLSX file, for example by using Excel to open and save as?)

[Kurt_Bremser]

By all means, save your file in XLSX format and use that. The XLSX engine works on all SAS platforms and does not suffer from any bitness issues.