Exporting multiple sas datasets into multiple excel sheets with macro?

excel sas export proc
user2993827 picture user2993827 · Apr 2, 2014 · Viewed 13.4k times · Source

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

Joe picture Joe · Apr 2, 2014

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.

Related questions

SAS script to list all SAS server users from metadata

I need a way to import a list of all SAS users from SAS metadata into an Excel worksheet. I was considering doing this using the SAS plugin for Microsoft Office to create a dynamic data source the retrieves the …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More