output data to excel with sheet name contains spac

问题:

data _null_;
    call symputx('ts','a b');
run;

proc export data=have
    outfile='path\file.xlsx';
    sheet="&ts.";
run;

But this will create a sheet named a_b(the original space is replaced by _.

How could this happen?

回答1:

That's related to how things work in SAS's proc export. What it's doing behind the scenes is creating a libname, and then creating a dataset. Under normal (validmemname=compat) rules, you may not have spaces in dataset names. There is an option (validmemname=extend) to tell SAS to allow spaces (Which you then use a named literal to access, namely, "a b"n (the n tells SAS it's a name), but it seems proc export (and libname itself) doesn't listen to that.

However, in the present day, there is a workaround for this: You can use dbms=xlsx in the export if you are on SAS 9.4 TS1M1 or later. This uses a different engine than the default excel (which uses Microsoft's JET engine), and it permits spaces easily.

回答2:

Just use the DBMS=XLSX option and you can include spaces in your sheet names.

proc export data=sashelp.class 
    outfile='class.xlsx'
    dbms=xlsx 
;
    sheet="A B";
run;