DATA Step, Macro, Functions and more

exporting multiple sas datasets into multiple sheets in one excel workbook

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

exporting multiple sas datasets into multiple sheets in one excel workbook

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


Accepted Solutions
Solution
‎09-19-2014 01:58 PM
Super User
Posts: 17,831

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

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.

View solution in original post


All Replies
Solution
‎09-19-2014 01:58 PM
Super User
Posts: 17,831

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

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.

Contributor
Posts: 25

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

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

New Contributor
Posts: 3

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

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

Super User
Super User
Posts: 6,500

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1468 views
  • 1 like
  • 4 in conversation