ODS Excel And Tabulate: Sheet Name

[MariaD]

Hi All,

I have a simple proc tabulate and ODS EXCEL to export to MS Excel. The result has a sheet by every segment. 

How I configure the ODS Excel statement to have a segment name as sheet name?

My current SAS code is:

ods excel file="/path/File_Test.xlsx"  options(sheet_interval='page');
    PROC TABULATE DATA=WORK.Test ; 
         CLASS Name Segment  &columnas / ORDER=UNFORMATTED MISSING;
         TABLE Segment, &columnas, Name*ColPctN ;
    RUN;
ods excel close;

But as results each sheet is named: Tabulate 1 - Table 1, Tabulate 2 - Table 1, etc. Please see the attached file.

Regards,

[Cynthia_sas · Accepted solution]

Hi, if you switch to BY group processing and use a BY instead of a page, then you can use the #byval for the sheet_name, as shown in this previous posting: https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excelxp-PROC-TABULATE-multiple-sheets/m-p/... -- there is an ODS EXCEL example in the posting on 5-17 that shows using #BYVAL to name the sheets.

cynthia

[MariaD]

Thanks @Cynthia_sas, works perfect!

[Reeza]

Try the Sheet_Label and Sheet_name options in the ODS EXCEL statement. 

You can find a lot of examples online using ODS TAGSETS which is the same approach you need for ODS EXCEL.

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data

http://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVer...

I'm getting some weird behaviour but maybe this will work for you:

proc sort data=sashelp.cars out=cars;
by origin;
run;

ods excel file="c:\_localdata\temp.xlsx" options(sheet_interval="bygroup" sheet_label='');

proc print data=cars;
by origin;
run;

ods excel close;