LIBNAME and xlsx

[GreggB]

My code is below. My question: is there a workaround when the name of the sheet you want to import has a space in it? The only thing I know to do is open it and, for example, rename "Worksheet Export" to "Worksheet"

libname x xlsx "G:\Courses\2021\sql_dev_export.xlsx";
data courses;
	set x.worksheet;
run;

[Kurt_Bremser · Accepted solution]

You can set

options validmemname=extend;

and then use

"Worksheet Export"n

as dataset name.

It is highly recommended to not use such name literals in further processing.

[GreggB]

The placement of "Worksheet Export"n evades me.

options validmemname=extend;
libname x xlsx "G:\Courses\2021\sql_dev_export.xlsx";
data sheet2;
	set x."Worksheet Export"n;
run;

60   options validmemname=extend;
61   libname x xlsx "G:\Courses\2021\sql_dev_export.xlsx";
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: G:\Departments\Research\Courses\2021\sql_dev_export.xlsx
62   data sheet2;
63       set x."Worksheet Export"n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File X.'Worksheet Export'n.DATA does not exist.
64
65   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SHEET2 may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
WARNING: Data set WORK.SHEET2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

[Kurt_Bremser]

I used the example sheet name from your initial post. If the sheet is actually called "Book 1", use "Book 1"n. With name literals, spelling is important, so "Book 1" is not the same as "BOOK 1", AFAIK.

[smantha]

libname x xlsx "C:\Users\mantsu01\Desktop\Book1.xlsx";
data courses;
	set x.'BOOK 1'n;
run;

[GreggB]

The name of the file is  Book1 (no space) and the name of the sheet is Book 1 (with a space)/

[ballardw]

An example of what I do with these, especially when there are multiple sheets:

options validvarname=v7 validmemname=extend;

libname source xlsx "<path>\filename.xlsx";
libname local "<some other path to work with the data";

proc copy in=source out=rst noclone;
run;

libname source clear;
proc datasets lib=local;
   change 
      "Sheet 1"n      =Sheet_1
     Tab3                =Summary_data
      "other name"n = Detail_data
   ;
run;
quit;

The validvarname option will force typical SAS variable names so none of the name literal needed.

This is based on an example where I was reading multiple sheets, so the Proc Copy makes copies in a local library that would be used for the project. Then used Proc datasets to change the names to something a bit nicer. If the tab doesn't have any characters that are not allowed in SAS data set names then you don't need to have the name literal. The names would be in the LOCAL library to look at for the proc data sets code. I also clear the Libname reference to the xlsx file as soon as practical so we don't have issues with locked files and such.

[Tom]

I had to deal with XLSX files with quirky table and column names that were not known in advance.  So I created a program to use PROC CONTENTS information to write code to copy all of the sheets.  It lets SAS try to create valid variable names using the VALIDVARNAME=V7 option, but includes a step to generate more user friendly variable names from the column headers.  If includes code to generate valid member names from the sheet names.

To use it first create a libref pointing to the XLSX file using the XLSX libname engine.  And another pointing to where you want to write the copied data.  This code assumes the source libref is A and the target libref is OUTA.

%let path=...\;
%let fnameA=A.xlsx;

libname A xlsx "&path.&fnameA";
libname outA "&path.sas";

* Set VALIDMEMNAME option to EXTEND to handle sheetnames with spaces ;
* Set VALIDVARNAME option to V7 ;
options validmemname=extend validvarname=v7;

* Get contents information from source workbook ;
proc contents data=A._all_ noprint out=contents;
run;
proc sort data=contents;
  by memname varnum;
run;

* Process the contents and use it to generate code to create datasets ;
filename code temp;
data contents;
  retain memname varnum name xlsxname type length label memlabel;
  set contents (keep=memname memlabel varnum name type length label );
  by memname ;

* Save current memname as quoted string in MEMLABEL to help with code gen;
  memlabel=quote(trim(memname),"'");

* Build valid memname from existing memname ;
  memname=translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,memname)),' _','_ ');
  memname=prxchange('s/(^[0-9])/_$1/',1,memname);

* Remove tabs, lf or cr from labels ;
  label=compbl(translate(label,' ','090A0D'x));

* Generate valid name from LABEL value ;
* Save name that the XLSX engine geneated when different;
  XLSXname=name;
  name=translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,label)),' _','_ ');
  name=prxchange('s/(^[0-9])/_$1/',1,name);
  if upcase(xlsxname)=upcase(name) then xlsxname=' ';

* Generate code to copy data from XLSX to permanent library ;
* Rename any variables where a better name was derived ;
* Remove formats and informats ;
  file code;
  if first.memname then put
     'data outa.' memname '( label=' memlabel ');'
   / '  set a.' memlabel +(-1) 'n (rename=('
  ;
  if xlsxname ne ' ' then put
     @4 xlsxname '=' name 
  ;
  if last.memname then put 
     '));'
   / '  if cmiss(of _all_)=' varnum 'then delete;'
   / '  format _all_;'
   / '  informat _all_;'
   / 'run;'
  ;

* Remove the qutoes from the member label ;
  memlabel=dequote(memlabel);
run;

/*
* Show generated code in SAS log ;
data _null_;
  infile code;
  input;
  put _infile_;
run;
*/

* Run generated code ;
%include code/source2;

* Print the contents information ;
proc print data=contents; 
  by memname memlabel ;
  id varnum name;
run;

I included generating a FORMAT statement to remove formats as SAS makes the mistake of attaching $xx formats to character variables.  You might want to remove that or modify it to not remove formats from any variables that are DATE, TIME or DATETIME values. Should be possible if you keep the FORMAT column from the PROC CONTENTS output and use the FMTINFO() function to check if the format attached is in one of those categories.