ODS Output to Excel - Naming the Excel Tab

buechler66 — Fri, 03 Apr 2020 12:34:18 GMT

Hi. I'm wondering if there is a way to name the Tab that is created within Excel that holds my Proc Report output? Right now it's getting populated with something by default.

Any help would be much appreciated.

/* EXPORT THE FILE TO XLS USING ODS */
OPTION MISSING = '';
ODS EXCEL FILE="C:\USERS\STEVEBUECHLER\BOX SYNC\IBM\USPS\HRC PAY DATA ANALYSIS\&SITE._HCR_PD_SUMMARY.XLSX";
PROC REPORT DATA=&SITE._HCR_SUMMARY_F;
title "&site_nm HCR Pay Data Summary";

define area  / style(column)=[cellwidth= 2in] ;

COMPUTE AREA;
      IF FIND(AREA,"AREA SITE GRAND TOTAL") THEN
      CALL DEFINE(_ROW_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
                                          FOREGROUND=BLUE]");
ENDCOMP;
COMPUTE PAY_DATA_MONTH_CHR;
      IF FIND(PAY_DATA_MONTH_CHR,"HCR CONTRACT TOTAL") THEN
      CALL DEFINE(_ROW_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
                                          BACKGROUND=HONEYDEW]");
ENDCOMP;
COMPUTE ACTIVITY_DESCRIPTION;
      IF FIND(ACTIVITY_DESCRIPTION,"CONTRACT SUBTOTAL") THEN
      CALL DEFINE(_COL_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
	                                      FOREGROUND=DARKGREEN
                                          BACKGROUND=HONEYDEW]");
ENDCOMP;
COMPUTE FREQ;
      IF FIND(ACTIVITY_DESCRIPTION,"CONTRACT SUBTOTAL") THEN
      CALL DEFINE(_COL_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
	                                      FOREGROUND=DARKGREEN
                                          BACKGROUND=HONEYDEW]");
ENDCOMP;
COMPUTE PAY_AMOUNT;
      IF FIND(ACTIVITY_DESCRIPTION,"CONTRACT SUBTOTAL") THEN
      CALL DEFINE(_COL_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
	                                      FOREGROUND=DARKGREEN
                                          BACKGROUND=HONEYDEW]");
ENDCOMP;
COMPUTE RATE_PER_UNIT;
      IF FIND(ACTIVITY_DESCRIPTION,"CONTRACT SUBTOTAL") THEN
      CALL DEFINE(_COL_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
	                                      FOREGROUND=DARKGREEN
                                          BACKGROUND=HONEYDEW]");
ENDCOMP;
COMPUTE UNITS;
      IF FIND(ACTIVITY_DESCRIPTION,"CONTRACT SUBTOTAL") THEN
      CALL DEFINE(_COL_, "STYLE", "STYLE=[FONTWEIGHT=BOLD
	                                      FOREGROUND=DARKGREEN
                                          BACKGROUND=HONEYDEW]");
ENDCOMP;
COLUMNS _ALL_;
RUN;
ODS EXCEL CLOSE;

Re: ODS Output to Excel - Naming the Excel Tab

PaigeMiller — Fri, 03 Apr 2020 12:45:47 GMT

There are dozens of options in ODS EXCEL, and you would be wise to familiarize yourself with them.

https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVersion=9.4&locale=en#p0258wnlz15ubrn1vqfalp6l4hjz

In particular, to answer your question

ods excel file="whatever.xlsx" options(sheet_name='Fred Flintstone');

Re: ODS Output to Excel - Naming the Excel Tab

buechler66 — Fri, 03 Apr 2020 12:51:04 GMT

Thanks for your help again! I really appreciate it. And thanks for the documentation link too.

Re: ODS Output to Excel - Naming the Excel Tab

Kurt_Bremser — Fri, 03 Apr 2020 13:03:04 GMT

@PaigeMiller wrote:

='Fred Flintstone'

YMMD

topic Re: ODS Output to Excel - Naming the Excel Tab in SAS Programming

ODS Output to Excel - Naming the Excel Tab

Re: ODS Output to Excel - Naming the Excel Tab

Re: ODS Output to Excel - Naming the Excel Tab

Re: ODS Output to Excel - Naming the Excel Tab