topic ODS Excel And Tabulate: Sheet Name in SAS Programming

ODS Excel And Tabulate: Sheet Name

MariaD — Tue, 20 Feb 2018 20:27:11 GMT

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,

Re: ODS Excel And Tabulate: Sheet Name

Cynthia_sas — Tue, 20 Feb 2018 20:57:04 GMT

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/359181/highlight/true#M18617 -- there is an ODS EXCEL example in the posting on 5-17 that shows using #BYVAL to name the sheets.

cynthia

Re: ODS Excel And Tabulate: Sheet Name

Reeza — Tue, 20 Feb 2018 20:57:56 GMT

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

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;

Re: ODS Excel And Tabulate: Sheet Name

MariaD — Wed, 21 Feb 2018 14:15:33 GMT

Thanks @Cynthia_sas, works perfect!