Exporting multiple sas datasets into multiple excel sheets with macro?

Question

I am looking if there is a macro that would export multiple datasets into separate excel worksheets within a workbook. Would be great if I could have 10 sheets per workbook.

I can do it the usual way as below, but I have more than 100 datasets to export:

PROC EXPORT DATA=HAVE;
OUTFILE= "S:\MYEXCEL.xlsx" 
        DBMS=EXCEL REPLACE;
SHEET="NEW_SHEET"; 
RUN;

Thank you!

Answer 1

The general concept is that you would do something like this:

%macro export_data(file=,data=,sheet=);
proc export data=&data.
outfile="&file."
dbms=excel replace;
sheet="&sheet.";
run;
%mend export_data;

Then you need to construct your export macro calls however you want. Get a dataset with one row per dataset (use dictionary.tables in SQL or sashelp.vtable in data step) and work out however you want to the logic of the sheet names and how many workbooks you need. Google data-driven macro calls for more information.