Sheet Name - proc export/excel engine - how to include blanks...

[xxformat_com]

When using excel engine or the sheet option of proc export, the sheet name can be specified using n-literal.

The downside is that the name is converting to SAS naming convention.

In the example, the blank is replaced with an underscore in the Excel file.

Is there a way of keeping the space?

libname demo excel "&xxdemo./reporting/test.xlsx";

data demo.'All Students'n; 
    set sashelp.class;
run;

libname demo clear;

[SASKiwi]

Try: options validvarname=any;

[Tom]

Set the VALIDMEMNAME option to EXTEND.

libname x xlsx 'c:\downloads\testblank.xlsx' ;
options validmemname=extend;
data x.'sashelp class'n ;
  set sashelp.class;
run;
libname x clear;

[xxformat_com]

Thanks. Nearly there. It works with xlsx engine but when trying it with excel engine, it does not.

[Tom]

Yet another good reason to not use the EXCEL engine.

[Reeza]

Excel is an older libname and designed for xls not xlsx files. The two do not have the same functionality and this is documented, do not expect the same functionality between the two.

In some cases, XLS has some more options and XLSX has more. I would resort to using XLSX by default as it's the more modern approach and the files. You can use a VB script to convert your workbooks to the other format if needed. 

---

@xxformat_com wrote:
Thanks. Nearly there. It works with xlsx engine but when trying it with excel engine, it does not.

---