How to Get PROC ODS TEXT on a new Tab in ODS EXCEL output

[adleach2]

Hi, 

    I am using SAS ODS EXCEL to create a multi tab excel spreadsheet.   I would like to have tabs with output and then some tabs with text.    I was going to use PROC ODSTEXT for the text, 

    However, I can't the PROC ODS TEXT output to appear on its own tab. 

   I have attached my code as well as the excel output.  

   Any suggestions on how to achieve this? 

Thanks,

Alan

[Reeza]

Can you please post your code to the forum rather than as an attachment? It makes it easier to answer your question.

Thanks!

I'm assuming you've tried setting sheet_interval to now before the PROC ODSTEXT via ODS EXCEL options?

https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVe...

NOW

creates a new worksheet. When used, the next output object starts on a new sheet. After SHEET_INTERVAL='NOW' is executed, the SHEET_INTERVAL option reverts to the previous setting.

Interaction	The option SHEET_INTERVAL='NONE' overrides the PAGEBREAK=NO option for PROC ODSTEXT and PROC ODSLIST. That is, SHEET_INTERVAL='NOW' creates a new worksheet when PROC ODSLIST or PROC ODSTEXT uses the PAGEBREAK=NO option.
Example	ods excel options (sheet_interval='now');

---

@adleach2 wrote:

Hi, 

    I am using SAS ODS EXCEL to create a multi tab excel spreadsheet.   I would like to have tabs with output and then some tabs with text.    I was going to use PROC ODSTEXT for the text, 

    However, I can't the PROC ODS TEXT output to appear on its own tab. 

   I have attached my code as well as the excel output.  

   Any suggestions on how to achieve this? 

Thanks,

Alan

---

[adleach2]

@Reeza Here is My codes as requested

data work_path;

work_path = pathname ( "WORK" ) ;

call symput ( "work_path", trim(work_path) ) ;
run;


ods excel file="&work_path./test.xlsx";


ods excel options(sheet_name="Output 1" sheet_interval="output");


proc print data=sashelp.class;
run;


ods excel options(sheet_name="TEXT" sheet_interval="output");

proc odstext ;

p 'NEED THIS ON A NEW TAB';

run;


ods excel options(sheet_name="Output 2" sheet_interval="output");
proc tabulate data=sashelp.class;
class age sex;
table age, sex;
run;


ods EXCEL close;

[adleach2]

Unfortunately it looks like I don't have the SHEET_INTERVAL="NOW" options available to me.

ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these keywords output, table, page, bygroup,
bygroups, proc, none or none.

We are on SAS  9.04.01M3P062415 

[adleach2 · Accepted solution]

However,  it the SHEET_INTERVAL="PROC" does put the PROC ODS TEXT on a new page. 

Thanks @Reeza for pointing me in the right direction. 

[Reeza]

9.4M3 is the first version where ODS Excel is in production. 

If you can upgrade at all, I'd highly recommend it. It's still quite buggy in that version.