PROC REPORT/RBREAK — adding text to numeric GROUP variable

[PaigeMiller]

Suppose I create a report using PROC REPORT

proc report data=sashelp.class; 
    columns age height weight;
	define age/group;
    define height/analysis mean format=10.2 "Height Mean";
    define weight/analysis mean format=10.1 "Weight Mean";
	rbreak after/summarize style=[font_style=italic];
run;

The report looks like this

but I want the bottom left cell (under Age, under 16) to have the text string 'All' inserted there. @Ksharp has shown me how to do this if AGE is a character variable. But in this case, AGE is numeric. I have tried creating a format such that a missing is replaced by 'All' and assigning that format to age. No success. I can't get it to work when AGE is a numeric variable. Can someone tell me how? Thanks!

[ChrisHemedinger]

I know you asked for REPORT and not TABULATE, but I can only figure out the answer for TABULATE.

proc tabulate
  data=sashelp.class

;
  var height weight;
  class age / order=unformatted missing;

  table /* row dimension */
    age 
      all={style={font_style=italic}}*{style={font_style=italic}},
      /* column dimension */
    height={label="Height (mean)"}*
      mean={label=""} 
      weight={label="Weight (mean)"}*
      mean={label=""};
    ;
run;

I'm sure a PROC REPORT expert will chime in with something different...

[PaigeMiller]

Thanks! I have a workaround where you run PROC SUMMARY to get the statistics, and then run PROC REPORT on the output of PROC SUMMARY, which honors the format on variable AGE. I know PROC SUMMARY well enough that I can get quick and correct answers with one hand tied behind my back. Also, I don't know PROC TABULATE. But, yes, a PROC REPORT answer would still be preferable.

[ChrisHemedinger]

I also don't know TABULATE well...but the Summary Tables task in SAS Enterprise Guide with its drag-and-drop interaction generated the code I shared. I didn't do anything to it except make it not ALL CAPS 😉

[Tom · Accepted solution]

Just do the exact same thing as for the character variable.

proc report data=sashelp.class; 
  columns age age2 height weight;
  define age/group noprint;
  define age2 / computed 'Age';
  define height/analysis mean format=10.2 "Height Mean";
  define weight/analysis mean format=10.1 "Weight Mean";
  compute age2 / character length=5 ;
    age2=put(age,5.);
  endcomp;
  compute after ;
    age2='Total';
  endcomp;
  rbreak after/summarize style=[font_style=italic];
run;

[FreelanceReinh]

Hello @PaigeMiller,

I've just found this solution/workaround accidentally while trying various options:

proc report data=sashelp.class; 
column age height weight;
define age/group mlf;
define height/analysis mean format=10.2 "Height Mean";
define weight/analysis mean format=10.1 "Weight Mean";
rbreak after/summarize style=[font_style=italic];
compute after;
  if _break_="_RBREAK_" then age="All";
endcomp;
run;

Then I remembered that @data_null__ mentioned the MLF trick earlier this year (not sure if it was this post).

Edit: Removed "format=3." as it was redundant.

[PaigeMiller]

Thanks @Tom and @FreelanceReinh . It seems like cheating to create a new column just to trick PROC REPORT into making the appearance of the output exactly what I want, but I have done it before. It seems like cheating to use the MLF option when you don't even have a multi-label format, but why not?

A slightly simpler version of the code from @FreelanceReinh 

proc report data=sashelp.class; 
column age height weight;
define age/group mlf;
define height/analysis mean format=10.2 "Height Mean";
define weight/analysis mean format=10.1 "Weight Mean";
rbreak after/summarize style=[font_style=italic];
compute after;
  age="All";
endcomp;
run;