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.
What SAS version and maintenance release are you using? You need to be on SAS 9.4M2 or higher for this to work:
Also SAS/ACCESS to PC Files needs to be installed and licensed.
Or could try other engine. libname xls xls '/u1/excel/Listing.xls'; libname xls excel '/u1/excel/Listing.xls';
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 ¯o 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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.