Hi. I have a Proc Summary dataset that I'm outputting to Excel using ODS and Proc Report. If you look at the attached picture you can see the current Proc Report output. Notice it's first row is an overall grand total. As such, the AREA variable is blank.
In an attempt to reduce the redundancy of repeating AREA names (GREAT LAKES) I added this line of code to the ODS Proc Report section of the code:
DEFINE AREA / GROUP;
This did remove the redundancy of AREA names (GREAT LAKES) and displays this AREA name only once. However, I'm also losing the overall grand total row at the top of the output in the attached pic where AREA was blank.
Is there a way I get the output to keep this top overall grand total row? Any help would be really 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" options(sheet_name="&SITE_NM");
PROC REPORT DATA=&SITE._HCR_SUMMARY_F;
TITLE "&site_nm HCR Pay Data Summary";
/*DEFINE AREA / GROUP;*/
/*DEFINE HCRID / GROUP;*/
/*DEFINE PAY_DATA_MONTH_CHR / GROUP;*/
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;
... View more
