topic Re: How to Get PROC ODS TEXT on a new Tab in ODS EXCEL output in ODS and Base Reporting

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

adleach2 — Thu, 30 Apr 2020 16:46:02 GMT

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

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

Reeza — Thu, 30 Apr 2020 17:10:37 GMT

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&docsetVersion=9.4&locale=en#p0258wnlz15ubrn1vqfalp6l4hjz

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

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

adleach2 — Thu, 30 Apr 2020 17:44:00 GMT

@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;

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

adleach2 — Thu, 30 Apr 2020 17:46:55 GMT

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

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

adleach2 — Thu, 30 Apr 2020 17:49:28 GMT

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.

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

Reeza — Thu, 30 Apr 2020 19:23:23 GMT

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.