topic Re: How to import second sheet from Excel workbook in to SAS without mentioning Sheet name in SAS Programming

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

Padma_stat — Tue, 14 Jul 2020 18:40:17 GMT

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

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

ketpt42 — Tue, 14 Jul 2020 18:51:40 GMT

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

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

Tom — Tue, 14 Jul 2020 19:19:58 GMT

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

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

Kurt_Bremser — Wed, 15 Jul 2020 07:46:41 GMT

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

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

ketpt42 — Wed, 15 Jul 2020 12:47:15 GMT

@Tom already mentioned that in his reply.

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

Padma_stat — Tue, 27 Oct 2020 11:59:12 GMT

its not working out for .xls files

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

Tom — Tue, 27 Oct 2020 12:09:08 GMT

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

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

Padma_stat — Tue, 27 Oct 2020 12:15:30 GMT

which option i need to change for reading .xls files

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

Tom — Tue, 27 Oct 2020 12:21:00 GMT

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

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

Kurt_Bremser — Tue, 27 Oct 2020 12:51:57 GMT

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.