ODS Proc Report Output Using DEFINE AREA / GROUP

buechler66 — Fri, 03 Apr 2020 14:26:39 GMT

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;

Re: ODS Proc Report Output Using DEFINE AREA / GROUP

Tom — Sun, 05 Apr 2020 21:18:48 GMT

GROUP columns have to come first. You probably want to explicitly list the column names in your COLUMNS statement instead of using a variable list. Otherwise you do not have control of the order of that the variables will appear.

topic Re: ODS Proc Report Output Using DEFINE AREA / GROUP in SAS Programming

ODS Proc Report Output Using DEFINE AREA / GROUP

Re: ODS Proc Report Output Using DEFINE AREA / GROUP