exporting multiple sas datasets into multiple sheets in one excel workbook

[esita]

Hi All,

I'm trying to export multiple sas datasets to excel where my multiple sas datasets are multiple sheets in 1 excel file. I wrote a macro for this. I'm sure I made some mistakes as I'm new to macro and still in learning phase. I have about 40 sas datasets. I would really appreciate if you could help me with this.

Thanks,

Esita

libname a "c:\sasdata";

%macro export_data(file=,data=,sheet=);

proc export data=&data.

outfile="&file."

dbms=excel replace;

sheet="&sheet.";

run;

%mend export_data;

%imp (file= "c:\sasdata\blood_result.xls",data= a.rbc,sheet= rbc);

%imp (file= "c:\sasdata\blood_result.xls",data= a.wbc,sheet= wbc);

[Reeza · Accepted solution]

You're calling the macro incorrectly.

You've named it export_data so replace %imp with %export_data.

Also, if you're using SAS Analytics U you'll need to make sure your path is UNIX compatible. The one above looks to be a windows path.

[esita]

Thanks, Reeza! That's so silly of me .

[SASJack]

Hi,

I tried following the similar steps but the exported xls is not showing any data. Please find my code below:

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

%mend export_data;
%export_data (file= "D:\SAS\export_files\multiexp.xls",data= sashelp.class,sheet= class);
%export_data (file= "D:\SAS\export_files\multiexp.xls",data= sashelp.cars,sheet= cars);
run;

thank you!!!

[Tom]

Start a new discussion.  You have added quotes to your filename twice. Once in the macro and once in the call.