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;
... View more
