BookmarkSubscribeRSS Feed
petlove
Obsidian | Level 7

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.

 

3 REPLIES 3
SASKiwi
PROC Star

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

 

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

 

Ksharp
Super User
Or could try other engine.

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

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


Tom
Super User Tom
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 3266 views
  • 0 likes
  • 4 in conversation