BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cxkev
Fluorite | Level 6


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,

1 ACCEPTED SOLUTION

Accepted Solutions
AncaTilea
Pyrite | Level 9

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

7 REPLIES 7
AncaTilea
Pyrite | Level 9

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.


cxkev
Fluorite | Level 6

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.

Peter_C
Rhodochrosite | Level 12

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

AncaTilea
Pyrite | Level 9

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.

cxkev
Fluorite | Level 6

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

Tim_SAS
Barite | Level 11

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;

brittneykp
Obsidian | Level 7

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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