BookmarkSubscribeRSS Feed
Takamini
Calcite | Level 5

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.

 

未命名.png

 

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;

1 REPLY 1
ballardw
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1323 views
  • 0 likes
  • 2 in conversation