topic Re: exporting multiple sas datasets into multiple sheets in one excel workbook in SAS Programming

exporting multiple sas datasets into multiple sheets in one excel workbook

esita — Fri, 19 Sep 2014 16:59:06 GMT

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);

Re: exporting multiple sas datasets into multiple sheets in one excel workbook

Reeza — Fri, 19 Sep 2014 17:58:41 GMT

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.

Re: exporting multiple sas datasets into multiple sheets in one excel workbook

esita — Fri, 19 Sep 2014 18:15:01 GMT

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

Re: exporting multiple sas datasets into multiple sheets in one excel workbook

SASJack — Thu, 15 Dec 2016 00:54:57 GMT

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!!!

Re: exporting multiple sas datasets into multiple sheets in one excel workbook

Tom — Thu, 15 Dec 2016 01:03:11 GMT

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