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;
Try: options validvarname=any;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.