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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.