The SAS Output Delivery System and reporting techniques

Export tagsets.ExcelXP to multiple files

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Export tagsets.ExcelXP to multiple files


Hi,

Does anyone know if it is possible to output the results of ODS to multiple excel workbooks in the same piece of code?

Sample code:

ods tagsets.excelxp file="d:\test\1.xls";

     proc print data=sashelp.heart;run;

ods tagsets.excelxp close;

Is there anyway a pieace of code could be added to the same submission to also output the reults in a file named d:\test\2.xls.

This is just a sample but in real life I have a program that outputs a total of 4 workbooks all identical with lots of options applied, everytime it gets changed, I need to update all 4 bits, I am hoping it's possible to only have to update it once!

Many Thanks for your time,


Accepted Solutions
Solution
‎01-29-2013 07:56 AM
Super Contributor
Posts: 543

Re: Export tagsets.ExcelXP to multiple files

Well, one quick way would be to write a small macro that takes as parameter  the name of your file.

For example, using your code:

%macro to_output(filename = );

ods tagsets.excelxp file="d:\test\&filename..xls";

     proc print data=sashelp.heart;run;

ods tagsets.excelxp close;

%mend to_output;


Call the macro:

%to_output(filename = first);

%to_output(filename = second);

%to_output(filename = third);

%to_output(filename = fourth);




OR


You could do this (which is a bit more compact)

%macro to_output( );

%do i = 1 %to 4;

ods tagsets.excelxp file="d:\test\file_&i..xls";

     proc print data=sashelp.heart;run;

ods tagsets.excelxp close;

%end;

%mend to_output;


Call the macro:


%to_output();


Good luck!

Anca.


View solution in original post


All Replies
Solution
‎01-29-2013 07:56 AM
Super Contributor
Posts: 543

Re: Export tagsets.ExcelXP to multiple files

Well, one quick way would be to write a small macro that takes as parameter  the name of your file.

For example, using your code:

%macro to_output(filename = );

ods tagsets.excelxp file="d:\test\&filename..xls";

     proc print data=sashelp.heart;run;

ods tagsets.excelxp close;

%mend to_output;


Call the macro:

%to_output(filename = first);

%to_output(filename = second);

%to_output(filename = third);

%to_output(filename = fourth);




OR


You could do this (which is a bit more compact)

%macro to_output( );

%do i = 1 %to 4;

ods tagsets.excelxp file="d:\test\file_&i..xls";

     proc print data=sashelp.heart;run;

ods tagsets.excelxp close;

%end;

%mend to_output;


Call the macro:


%to_output();


Good luck!

Anca.


Contributor
Posts: 28

Re: Export tagsets.ExcelXP to multiple files

Excellent, both solutions work perfectly.

As soon as I figure out how to flag the post as Answered I will oblige.

It seems to be stuck on Not Answered at the moment however and I don't know how to change.

Valued Guide
Posts: 2,175

Re: Export tagsets.ExcelXP to multiple files

cxkev

although now marked as answered, I think the solution that Tim offers must be the preferred option in general (those seeking macro solutions for everything should seek in a separate forum). Running the PROC just once must surely be the preferred approach. 

I Don't know how but hope there is some way on the forum, to flag a superior solution when it arrives after a "not quite so effective" answer has been selected as the "answer".

Is it possible already?

peterC

Super Contributor
Posts: 543

Re: Export tagsets.ExcelXP to multiple files

Hi

I agree that Tim's solution is the preferred one.

I was not aware of being able to write several ODS TAGSETS.EXCEXP in one run.

My idea was that the user that posted the question needed to call ODS TAGSETS several times...the one way - I thought - some thing that is needed repeatedly could be accomplished is via macro call.

(I don't particularly prefer a macro to everything)

Cheers!

Anca.

PS: I thought we all are trying to offer alternatives, and options on how to solve a given problem.

Contributor
Posts: 28

Re: Export tagsets.ExcelXP to multiple files

Good Morning Peter,

I hope I have not caused any offense, I am just delighted that we have a community of users dedicated to helping fellow  SAS users.

While I appreciate both answers deeptly, Anca's solution removes the need for me to set all of the options multiple times. As far as I can see (Although I must admit, I'm not an expert, so may not be unserstanding all the syntax!) Tim's solution is opening multiple ODS files and therefore will require multiple options statements.

In my opinion, because the capabailities of SAS are so vast, I think there are many scenarios where there may well be more than 1 correct answer.

This was my first post on the site so apologise if I have not adhered to the rules 100%. I hope I have gave sufficient justification for my actions and I also hope that this does not deter users from offering assistance to me in the future, because I appreciate all of the answers.

Appreciate any feedback,

Thanks

cxkev

Super Contributor
Posts: 394

Re: Export tagsets.ExcelXP to multiple files

You can have multiple instances of the ExcelXP tagset in use simultaneously. This bit of code writes the same PROC PRINT output to 4 workbooks at once:

ods tagsets.excelxp file="1.xls";

ods tagsets.excelxp(2) file="2.xls";

ods tagsets.excelxp(3) file="3.xls";

ods tagsets.excelxp(4) file="4.xls";

proc print data=sashelp.class;

run;

ods _all_ close;

Occasional Contributor
Posts: 10

Re: Export tagsets.ExcelXP to multiple files

Thank you, I've been hunting through documentation and online articles for almost an hour trying to find out if you can designate more than one ODS destination location!  Although I have so many options set for mine, that I think the Macro answer might be simpler, but this answers a long held question I've had about saving the same output to multiple locations. 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 544 views
  • 8 likes
  • 5 in conversation