The SAS Output Delivery System and reporting techniques

ODS excel multiple sheet

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

ODS excel multiple sheet

[ Edited ]

Dear all,

 

I am writing an excel report by using the new experimental ods dsestination: EXCEL.

 

I simply would like to know if it is possible to write into several sheet, then same way excel tagset does: by calling ods excel with a different sheet_name option:

 

ods excel (id=sheet1) file=xlsheet options(sheet_interval="none" sheet_name="summary1");

PROC REPORT 1

....

 

ods excel (id=sheet2) options(sheet_name="summary2") ;

PROC REPORT 2

....

 

ods _all_ close ;

 

 

So far I only manage to get the last report to be displayed and I don't want to use PROC EXPORT.

Thank you for your help,

 

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Super User
Posts: 7,407

Re: ODS excel multiple sheet

This works fine for me:

ods excel file="s:\temp\rob\temp.xlsx" options(sheet_name="sheet1");
proc print data=sashelp.cars;
run;
ods excel options(sheet_name="sheet2");
proc print data=sashelp.class;
run;
ods excel options(sheet_name="sheet3");
proc print data=sashelp.cars;
run;
ods excel close;

View solution in original post


All Replies
Solution
‎09-25-2015 06:23 AM
Super User
Super User
Posts: 7,407

Re: ODS excel multiple sheet

This works fine for me:

ods excel file="s:\temp\rob\temp.xlsx" options(sheet_name="sheet1");
proc print data=sashelp.cars;
run;
ods excel options(sheet_name="sheet2");
proc print data=sashelp.class;
run;
ods excel options(sheet_name="sheet3");
proc print data=sashelp.cars;
run;
ods excel close;

Occasional Contributor
Posts: 6

Re: ODS excel multiple sheet

Thank you very much for the solution, I just removed the id statement and then it worked smoothly !

 

Smiley Happy

SAS Super FREQ
Posts: 8,743

Re: ODS excel multiple sheet

Hi:

  ID= is meant to be used when you are opening multiple instances of the SAME destination, which is not what you needed to do. That's why it did not work in your case. A good example of using ID= with ODS Excel and ID= is shown below:

        

ods excel(id=one) file='c:\temp\out1_sasweb.xlsx' style=sasweb
    options(sheet_name='first_in_one');
ods excel(id=two) file='c:\temp\out2_htmlblue.xlsx' style=htmlblue
    options(sheet_name='first_in_two');

 

proc print data=sashelp.class;
run;

 

ods excel(id=one) options(sheet_name='xxx');
ods excel(id=two) options(sheet_name='yyy');

    

proc print data=sashelp.shoes(obs=3);
run;

ods _all_ close;

 

  So, in this example code, 2 separate files (with different names) are created from the same procedures using the SAME destination, ODS EXCEL. Each file used a different style and each file has different names for the tabs. So the sheet names for the out1_sasweb.xlsx file will be first_in_one and xxx and the sheet names for the out2_htmlblue.xlsx will be first_in_two and yyy.

 

cynthia

Occasional Contributor
Posts: 6

Re: ODS excel multiple sheet

Thank you Cynthia, this is a very clear example!

 

Back to my problem, 

 

I am using ods text to push text into the excel report, but something strange happend, the text is going into the first tab, which is not what I want. Do you have maybe an idea why ?

 

ods excel options(
sheet_name="Prog summary"
);
/* Datasets detailed report */
ods text="~S={font_size=14pt font_weight=bold}~ blop3" ;
ods text="~S={font_size=14pt font_weight=bold}~ blop4" ;
ods text="~S={font_size=14pt font_weight=bold}~ Summary of datasets" ;

Thank you !

Regular Learner
Posts: 1

Re: ODS excel multiple sheet

Did you ever get a solution to the problem of ods text titles?

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 6008 views
  • 2 likes
  • 4 in conversation