Changing PROC REPORT DEFINE Labels across pages.

[SmithCJGVSU]

In pharmaceutical - clinical trial reporting, we often present Big N values in the PROC REPORT column headers.  To do this, we may opt to create macro variables and use them in the DEFINE statement:

proc sql noprint;
    /*Big N*/
    select trt01pn, count(distinct usubjid) into :trtn1-:trtn99,:bign1-:bign99
    from adsl
    group by trt01pn;    
quit;

ODS TAGSETS.RTF <...>;
proc report data=final missing split='|' ;
    columns varord varlbl statord statlbl col:;
    define varord /order order=internal noprint;
    define varlbl /order order=internal noprint;
    define statord /order order=internal noprint;    
    define statlbl /display '' style(column)={cellwidth=2.21in} id;
    define col1   /display style(column)={cellwidth=1in} "Placebo|(N=&bign1)";
    define col2   /display style(column)={cellwidth=1in} "Active 1|(N=&bign2)";
    define col3   /display style(column)={cellwidth=1in} "Active 2|(N=&bign3)";
    /* <other proc report statements> */
run;
ODS TAGSETS.RTF close;

The difficulty though, is if we introduce By-Group processing (e.g. BY SEXN SEX;) and we want page 1 to reflect Males and page 2 to reflect Females, we would also want the Big N values to based on the subgroup.  So, Big N values can not be "hard-coded" via DEFINE label unless we do multiple PROC REPORT procedures.  I know we can't use #BYVAL in the DEFINE statement, but is there alternatives where we can change the BIG N values across pages WITHOUT doing multiple PROC REPORT procedures (i.e. one procedure per subgroup level)?  Note that, all the data are already summarized prior to PROC REPORT.  We are using REPORT as a glorified PROC PRINT.  See Screenshots below as an example.  The goal is to get the counts in the column header to pertain to the subgroup without doing the brute force method of multiple report procedures.

[ed_sas_member]

Hi @SmithCJGVSU 

In this particular case, an alternative to BY group processing could be the use of macro language.

Maybe something like this:

1 - definition of distinct macrovariables by group:

proc sql;
    /*Big N overall*/
    select trt01pn, count(distinct usubjid) into :trtn1-:trtn99,:bign1-:bign99
    from adsl
    group by trt01pn;
    /*Big N sexn=0*/
    select trt01pn, count(distinct usubjid) into :trtn_0_1-:trtn_0_99,:bign_0_1-:bign_0_99
    from adsl
    where sexn=0
    group by trt01pn;
    /*Big N sexn=1*/
    select trt01pn, count(distinct usubjid) into :trtn_1_1-:trtn_1_99,:bign_1_1-:bign_1_99
    from adsl
    where sexn=1
    group by trt01pn;
quit;

2 - Define a macro program with sex as a parameter (nb: you should also define the title accordingly, with values of sex as it is less efficient than BY group processing to do that automatically)

%macro report(sex=);

	proc report data=final missing split='|' ;
	    columns varord varlbl statord statlbl col:;
	    define varord /order order=internal noprint;
	    define varlbl /order order=internal noprint;
	    define statord /order order=internal noprint;    
	    define statlbl /display '' style(column)={cellwidth=2.21in} id;
	    define col1   /display style(column)={cellwidth=1in} "Placebo|(N=&&&bign_&sex._1)";
	    define col2   /display style(column)={cellwidth=1in} "Active 1|(N=&&&bign_&sex._2)";
	    define col3   /display style(column)={cellwidth=1in} "Active 2|(N=&&&bign_&sex._3)";
	run;

%mend;

3 - Macro call (one per group like below, or use of CALL EXECUTE)

%report (sex=0)
%report (sex=1)