Wrong sheet names in libname xlsx

[msauer]

I have/had a problem concerning the import of excel files, for which I don't know the sheet names. So I usually proceed by

1. getting a dataset with all the sheet names for that file and
2. run an import macro (the specifics don't matter here) via call execute on that dataset.

Part a. is done as follows

%let file = path_to_file;
libname xlsxtmp xlsx "&file.";

proc contents data=xlsxtmp._all_ out=work.sheets noprint;
run;

data work.sheets;
  set work.sheets;
  by memname;
  if first.memname then do;
    sheet = memname;
    output;
  end;
  keep sheet;
run;

libname xlsxtmp clear;

The excel files I have to import come from a lot of different sources and I recently got an error

ERROR: Couldn't find sheet in spreadsheet.

in the Proc Contents.

This was due to a sheet with a trailing blank in the sheet name (yes, Excel allows that). I attached a file for demonstration. The xlsx-Engine just strips this trailing blank. Since I use SAS EG, I just tried the

libname xlsxtmp xlsx "&file.";

 by itself. All sheets are shown in die library.

• Clicking on the one with the trailing blank in the name ("sheet with trailing blank") to open it in EG results in an error.
• Copying the sheet with a datastep fails using the correct sheet name (without the trailing blank it fails, too)

  set xlsxtmp.'sheet with trailing blank 'n;

• Proc import on that file works, when sheet is set to the correct name with the trailing blank (without it if fails)
  

  proc import file="&file." dbms=xlsx out=work.import replace;
    sheet="sheet with trailing blank ";
  run;

SAS version 9.04.01M4

EG version 7.13HF5

[Quentin]

Sounds like you've done a thorough round of testing.  I would suggest sending this in to tech support. to see if they confirm or have a fix.

[Kurt_Bremser]

I also tried everything I could imagine, with no success. Even this fails:

proc copy in=xlsxtmp out=work;
run;

where SAS should automatically use the names as it finds them.

Bottom line: remove the blank in Excel before import.

Final bottom line: another illustration why Excel files MUST NOT BE USED FOR DATA TRANSFER.

[andreas_lds]

I am not allowed to download files, so i created one and got the same error message you have posted.

So please contact sas tech support, this seems to be a bug.

SAS: 9.04.01M7

[msauer · Accepted solution]

I have a workaround using the excel engine instead of xlsx. This produces different sheet names that are also not "bulletproof".

libname xlsxtmp excel "&file.";

Using Proc Contents on this library fails, when one of the sheets has a single quote in the name. It's the same in the EG, that clicking on this sheet results in an error. The reason for this is that single quotes are masked by two single quotes, i. e. 

sheet'1 >>> sheet''1

Fortunately, this is easier to work around than a missing trailing blank.

• Retrieve list of sheets with Proc Datasets
  

  ods output members=work.sheets;
  proc datasets lib=xlsxtmp memtype=data;
  quit;

• Sheet names with non-alphanumeric characters(except a single quote) are enclosed in single quotes. In order to have a consistent behaviour just quote those which aren't already quoted.
• Remove the trailing $-sign.

In subsequent processing you have to be careful about the single and double quotes. For instance, I use Proc Import with the Range option

proc import file="&file." dbms=xlsx out=work.import;
  getnames=no;
  range="%bquote(&sheet.)$&start.:&end.";
run;

For this to work, you have to replace

• two single quotes by one single quote and
• one double quote by two double quotes

in the sheet name.

[msauer]

Further testing revealed additional problems with the excel-engine.

• Dots (.) are replaced by #.
• ! are replaced by _.

So the best solution I have found thus far is the following:

• extract sheet names with both engines,
• normalize the sheet names such that the two datasets can be merged,
• add the leading and trailing spaces to the names provided by the xlsx engine.

/* Get sheet names via xlsx. */
libname tmp xlsx "&file.";
ods output members=work.xlsx;
proc datasets lib=tmp memtype=data;
run;
libname tmp clear;

/* Get sheet names via excel. */
libname tmp excel "&file.";
ods output members=work.excel;
proc datasets lib=tmp memtype=data;
run;
libname tmp clear;

/* Normalize sheet names for merge. */
data work.xlsx;
  set work.xlsx;
  length sheet $ 50 sheet_merge $ 50;
  sheet = name;
  sheet_merge = upcase(tranwrd(tranwrd(name, ".", "#"), "!", "_");
  keep sheet sheet_merge;
run;

data work.excel;
  set work.excel;
  length sheet_merge $ 50 leading_spaces 3 trailing_spaces 3;
  if substr(name, length(name), 1) = "$" then
    /* Trailing $ when no space in sheet name. */
    do;
      sheet_merge = upcase(strip(substr(name, 1, length(name) - 1)));
      leading_spaces = 0;
      trailing_spaces = 0;
    end;
  else if substr(name, length(name) - 1, 2) = "$'" then
    /* If spaces in sheet name, then sheet name is enclosed by single quotes. */
    do;
      sheet_merge = upcase(strip(substr(name, 2, length(name) - 3)));
      /* Single quotes have been doubled. */
      sheet_merge = tranwrd(sheet_merge, "''", "'");
      leading_spaces = length(substr(name, 2)) - length(strip(substr(name, 2)));
      trailing_spaces = lengthc(substr(name, 1, length(name) - 2)) - lengthc(strip(substr(name, 1, length(name) - 2)));
    end;
  /* Named ranges are included without trailing $. Omit them. */
  if not missing(sheet_merge) then
    output;
  keep sheet_merge leading_spaces trailing_spaces;
run;

proc sort data=work.xslx;
  by sheet_merge;
run;
proc sort data=work.excel;
  by sheet_merge;
run;

data work.sheets;
  merge work.xlsx work.excel;
  by sheet_merge;
  /* Enclose name with [] (not allowed by Excel). */
  sheet = cats("[", sheet, "]");
  /* Add the spaces. */
  do i = 1 to leading_spaces;
    sheet = tranwrd(sheet, "[", "[ ");
  end;
  do i = 1 to trailing_spaces;
    sheet = tranwrd(sheet, "]", " ]");
  end;
  /* Enclose with single quotes and escape single quotes in the sheet name. */
  sheet = tranwrd(sheet, "'", "''");
  sheet = tranwrd(sheet, "[", "'");
  sheet = tranwrd(sheet, "]", "'");
  keep sheet;
run;

[ballardw]

Did you try setting Option Validmemname=Extend; ?

Not stated explicitly so thought it might be worth checking.

[msauer]

I did not set this explicitly but the behaviour does not change. Maybe this option is already set implicitly with the libname statement, seeing that the names of datasets (=sheets) contain blanks and special characters.