topic Re: Sheet Name - proc export/excel engine - how to include blanks... in SAS Programming

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

xxformat_com — Wed, 01 Apr 2020 22:15:50 GMT

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;

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

SASKiwi — Wed, 01 Apr 2020 23:22:57 GMT

Try: options validvarname=any;

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

Tom — Wed, 01 Apr 2020 23:27:14 GMT

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;

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

xxformat_com — Wed, 01 Apr 2020 23:47:58 GMT

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

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

Tom — Thu, 02 Apr 2020 00:30:46 GMT

Yet another good reason to not use the EXCEL engine.

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

Reeza — Thu, 02 Apr 2020 01:34:20 GMT

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.