Excel sheet name

petlove — Fri, 03 Aug 2018 21:20:39 GMT

Hi all,

I need to get excel sheet names into macro variable. Can anybody help me how to do that?

I tried using below code:

libname xls xlsx '/u1/excel/Listing.xlsx';

But it shows below error:

ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

Thank you.

Re: Excel sheet name

SASKiwi — Fri, 03 Aug 2018 23:14:47 GMT

What SAS version and maintenance release are you using? You need to be on SAS 9.4M2 or higher for this to work:

http://documentation.sas.com/?docsetId=acpcref&docsetTarget=acpcrefwhatsnew94.htm&docsetVersion=9.4&locale=en

Also SAS/ACCESS to PC Files needs to be installed and licensed.

Re: Excel sheet name

Ksharp — Sat, 04 Aug 2018 10:56:38 GMT

Or could try other engine.

libname xls xls '/u1/excel/Listing.xls';

libname xls excel  '/u1/excel/Listing.xls';

Re: Excel sheet name

Tom — Sat, 04 Aug 2018 21:38:24 GMT

I had a macro that could read the sheet names from the XML data inside the XLSX file that I used before SAS released the XLSX engine.

%macro xlsx
/*----------------------------------------------------------------------
Macro for reading sheets from XLSX files
----------------------------------------------------------------------*/
(filename       /* Physical name of XLSX file to read. */
,sheets=        /* Dataset name to hold list of sheets.  */
,import=        /* Run code to import sheets? (0/1) */
,libref=        /* Where to write the imported sheets. (default=WORK) */
,getnames=      /* Setting of GETNAMES option. (yes/no) (default=yes) */
,hidden=        /* Include sheets with state="hidden"? (0/1) */
);
%local macro parmerr nsheets ;
%let macro=&sysmacroname;
%let parmerr=0;

%*----------------------------------------------------------------------
Check if using recent enough version of SAS.
-----------------------------------------------------------------------;
%if %sysevalf(&sysver < 9.4) %then %parmv(_msg=Requires SAS 9.4 or up);
%if (&parmerr) %then %goto quit;

%*----------------------------------------------------------------------
Validate user parameters.
-----------------------------------------------------------------------;
%if (%bquote(&filename)=) %then %parmv(_msg=Filename is required);
%else %do;
  %if ^%sysfunc(fileexist(&filename)) %then
    %parmv(_msg=Filename &filename does not exist) ;
  %else %let filename=%sysfunc(quote(%qsysfunc(dequote(&filename)))) ;
%end;

%parmv(import,_val=0 1,_def=%eval(0<%length(&libref)))
%parmv(libref,_def=work)
%if %length(&libref)>8 %then %parmv(LIBREF,_msg=Maximum length of 8);
%else %if %sysfunc(libref(&libref)) %then
  %parmv(LIBREF,_msg=&libref is not assigned)
;

%parmv(sheets,_req=0)
%parmv(getnames,_val=yes no,_case=l,_def=yes)
%parmv(hidden,_val=0 1,_def=0)

%if (&parmerr) %then %goto quit;

*----------------------------------------------------------------------;
* 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 pipe %sysfunc(quote(unzip -p &filename xl/workbook.xml));
libname _wb xmlv2 xmlmap=_wbmap ;

*----------------------------------------------------------------------;
* Read sheet names from XLSX file into a SAS dataset. ;
* Create valid SAS dataset name from sheetname or sheetnumber. ;
* Generate PROC IMPORT code to convert sheets into datasets. ;
*----------------------------------------------------------------------;
filename extract temp;
data &sheets ;
  file extract ;
  if _n_=1 then do ;
    put "* Generated by &macro macro @%sysfunc(datetime(),datetime19.);" ;
  end;
  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 File
name' ;
  filename = &filename ;
  if ^nvalid(compress(sheet),'v7') then memname = cats('Sheet',number);
  else memname = translate(trim(compbl(sheet)),'_',' ');
%if (^&hidden) %then if state ne 'hidden' then ;
  put
    / 'proc import datafile=' %sysfunc(quote(&filename)) ' dbms=xlsx'
    / "  out=&libref.." memname '(label=' sheet :quote. ') replace'
    / ';'
    / "  getnames=&getnames;"
    / @3 sheet= :$quote. ';'
    / 'run;'
  ;
run;
%let sheets=&syslast;

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

%if (&import) %then %do;
*----------------------------------------------------------------------;
* Run the generated PROC IMPORT code. ;
*----------------------------------------------------------------------;
  %inc extract ;
%end;

%*----------------------------------------------------------------------
Generate summary messages for user to the SASLOG .
-----------------------------------------------------------------------;
%if ^&nsheets %then %parmv(_msg=No worksheets found in &filename);
%else %do;
  %put ;
  %put NOTE: Worksheet list successfully read from file &filename.. ;
  %put NOTE: &sheets has information on &nsheets worksheets.;
  %if ^&import %then %put NOTE: PROC IMPORT code is in fileref EXTRACT.;
  %else %put NOTE: Worksheets were imported to &libref library.;
  %put ;
%end;

%quit:
%mend xlsx ;

If you want to try it you can find a copy of the parameter validation utility, %parmv(), here:

https://github.com/sasutils/macros/blob/master/parmv.sas

topic Re: Excel sheet name in SAS Programming

Excel sheet name

Re: Excel sheet name

Re: Excel sheet name

Re: Excel sheet name