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

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,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

proc_sortt
Calcite | Level 5

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

 

🙂

Cynthia_sas
SAS Super FREQ

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

proc_sortt
Calcite | Level 5

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 !

krritter
Calcite | Level 5

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 5 replies
  • 86018 views
  • 17 likes
  • 4 in conversation