DATA Step, Macro, Functions and more

ODS Excel And Tabulate: Sheet Name

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

ODS Excel And Tabulate: Sheet Name

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,


Accepted Solutions
Solution
‎02-21-2018 09:14 AM
SAS Super FREQ
Posts: 9,365

Re: ODS Excel And Tabulate: Sheet Name

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

View solution in original post


All Replies
Solution
‎02-21-2018 09:14 AM
SAS Super FREQ
Posts: 9,365

Re: ODS Excel And Tabulate: Sheet Name

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
Frequent Contributor
Posts: 84

Re: ODS Excel And Tabulate: Sheet Name

Posted in reply to Cynthia_sas

Thanks @Cynthia_sas, works perfect!

Super User
Posts: 23,663

Re: ODS Excel And Tabulate: Sheet Name

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 257 views
  • 0 likes
  • 3 in conversation