Sheet names in ODS TAGSETS.EXCELXP

Takamini — Wed, 20 Sep 2017 08:01:32 GMT

Dear All,

How to set up sheet name when using proc report to generate excel?

For example, I want to make the sheet name as TOC, L16_2_1_1 and L16_2_1_2.

I don't want the "Table" in the sheet name.

Below is my code:

ODS TAGSETS.EXCELXP FILE="&outpath.\Study_Listing_&time..XLS" STYLE=Styles.mystyle;

ods escapechar='^';

PROC REPORT DATA= result.L_title NOWD
COLWIDTH=8 SPACING=2 /* Default column width & spacing */
CENTER /* Set default alignment to center */
HEADSKIP /* A blank under column headers. */
SPLIT= '|'
LS=256
contents="TOC"; /* Let split/new line char as '|' instead of the default '/'. */

COLUMNS pp no title number item;
DEFINE pp / display noprint;
DEFINE no / display noprint;
DEFINE title / display LEFT style=[cellwidth=80mm] '' '' flow;
DEFINE number / DISPLAY LEFT style=[cellwidth=36mm] 'No.' '';
DEFINE item / DISPLAY LEFT style=[cellwidth=120mm] 'Item' '' flow;
TITLE7 'Table of Contents';
RUN;
TITLE7 ' ';
QUIT;

PROC REPORT DATA=result.L16_2_1 NOWD
COLWIDTH=8 SPACING=2
CENTER
HEADSKIP
SPLIT= '|'
LS=256
contents="L16_2_1_1";
COLUMNS pp ptno TRT VISDAT VISDAT2 VISDAT3 VISDAT4 VISDAT5 DSTERM DSDTC DSSDTC DSDECOD DEDECOD2 DSCO ;
DEFINE ptno /display center style=[cellwidth=13mm] 'Subject|No.';
DEFINE trt /display center style=[cellwidth=18mm] 'Treatment';
DEFINE VISDAT /display center style=[cellwidth=24mm] 'Screening|(YYYYMMDD)' ;
DEFINE VISDAT2 /display center style=[cellwidth=24mm] 'Baseline|(YYYYMMDD)' ;
DEFINE VISDAT3 /display center style=[cellwidth=24mm] 'Month 1|(YYYYMMDD)' ;
DEFINE VISDAT4 /display center style=[cellwidth=24mm] 'Month 5|(YYYYMMDD)' ;
DEFINE VISDAT5 /display center style=[cellwidth=24mm] 'End of Study|(YYYYMMDD)' ;
DEFINE DSTERM /display center style=[cellwidth=16mm] 'Did the Subject Complete the Study?' ;
DEFINE DSDTC /display center style=[cellwidth=30mm] 'If Yes, Date of Completion|(YYYYMMDD)' f=IS8601DA10. ;
DEFINE DSSDTC /display center style=[cellwidth=25mm] 'If No, Date of Discontinuation|(YYYYMMDD)' ;
DEFINE DSDECOD /display left style=[cellwidth=50mm] 'If No, Please Choose Only One Primary Reason' ;
DEFINE DEDECOD2 /display left style=[cellwidth=100mm] 'Please Specify the Details About the Reason for Discontinuation' ;
DEFINE DSCO /display left style=[cellwidth=100mm] 'Comment' ;
DEFINE pp / ORDER noprint;
TITLE7 j=center height=10pt "Listing 16.2.1.1: Discontinued Subjects Disposition";
FOOTNOTE4 j=left height=10pt "Note:" ;
FOOTNOTE5 j=left height=10pt "*Other, please specify" ;
FOOTNOTE6 j=left height=10pt "**How many cigarettes per day" ;
RUN;
TITLE7 " ";
FOOTNOTE4 " ";
FOOTNOTE5 " ";
FOOTNOTE6 " ";
QUIT;

PROC REPORT DATA= result.L16_2_2_1 NOWD
COLWIDTH=8 SPACING=2
CENTER
HEADSKIP
SPLIT= '|'
LS=256
contents="L16_2_1_2";
COLUMNS pp ptno TRT IEYN ('If No, Please Specify the Reason' IENO EXNO IECO ) ;
DEFINE ptno /display center style=[cellwidth=25mm] 'Subject No.';
DEFINE trt /display center style=[cellwidth=18mm] 'Treatment';
DEFINE IEYN /center style=[cellwidth=90mm] 'Does the Subject Meet All of the Inclusion Criteria and None of the Exclusion Criteria?' ;
DEFINE IENO /left style=[cellwidth=40mm] 'Inclusion Criteria No.' ;
DEFINE EXNO /left style=[cellwidth=40mm] 'Exclusion Criteria No.' ;
DEFINE IECO /display left style=[cellwidth=40mm] 'Comment' ;
DEFINE pp / ORDER noprint;
break before pp / page contents='' ;
TITLE7 j=center height=10pt "Listing 16.2.2.1: Inclusion/ Exclusion Criteria at Screening";
RUN;
TITLE7 ' ';
QUIT;

ODS TAGSETS.EXCELXP CLOSE;

Re: Sheet names in ODS TAGSETS.EXCELXP

ballardw — Wed, 20 Sep 2017 15:09:59 GMT

Before EACH call to proc report insert a line like

ods tagsets.excelxp options(sheet_name='TOC');

the text you want for the specific sheet name would replace TOC for the second or third pages.

topic Re: Sheet names in ODS TAGSETS.EXCELXP in SAS Programming

Sheet names in ODS TAGSETS.EXCELXP

Re: Sheet names in ODS TAGSETS.EXCELXP