How to use PROC EXPORT to export to multiple tabs in Excel

Question

I have a handful of SAS datasets in my project file, which I would like to combine into one spreadsheet - each dataset on a different tab. I found a solution here: https://communities.sas.com/thread/58447 but it is not working for me. The code I am using is:

proc export data=work.dod_ltd file='H:\Projects\DOD_Report\Sample.xlsx' dbms=xlsx; sheet='LTD Detail';
proc export data=work.dod_std file='H:\Projects\DOD_Report\Sample.xlsx' dbms=xlsx; sheet='STD Detail';
proc export data=work.dod_life_waiver file='H:\Projects\DOD_Report\Sample.xlsx' dbms=xlsx; sheet='Waiver Detail';
run;

However, instead of adding new tabs, each proc export is overwriting the existing 'Sample.xlsx' file, so I end up with only the 'Waiver Detail' tab.

How do I make it add the tabs to an existing spreadsheet?

Thanks!

Mike

Solution 1

XLSX only supports multiple sheets per workbook in the later versions, SAS 9.4+. For earlier versions the answer may vary but try changing your DBMS. The DBMS options depend on your version of SAS, your version of Excel as well as the bitness (32/64bit) of each application.

 DBMS=EXCELCS

Solution 2

You have forgot to put run statements after every proc export and also add replace after DBMS statement and it should work.

Comments

[mjavon]

Thanks again, you wouldn't happen to also know how to get the columns to autofit, would you?

[Reeza]

Unfortunately that isn't available via Proc Export. If you're interested in formatting your Excel output you need to look at other export options, primarily ODS TAGSETS.EXCELXP if you're on SAS 9.3

[Robert Penridge]

@mjavon You should post a new question re. 'autofit' rather than throwing it in here as a comment. It will be more useful as a question and allow people to answer it in the detail it deserves.