topic Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable in ODS and Base Reporting

PROC REPORT/RBREAK — adding text to numeric GROUP variable

PaigeMiller — Tue, 06 Sep 2022 16:37:21 GMT

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!

Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable

ChrisHemedinger — Tue, 06 Sep 2022 19:01:42 GMT

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...

Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable

PaigeMiller — Tue, 06 Sep 2022 19:17:07 GMT

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.

Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable

ChrisHemedinger — Tue, 06 Sep 2022 19:26:17 GMT

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 😉

Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable

Tom — Tue, 06 Sep 2022 20:01:21 GMT

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;

Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable

FreelanceReinh — Tue, 06 Sep 2022 20:23:22 GMT

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.

Re: PROC REPORT/RBREAK — adding text to numeric GROUP variable

PaigeMiller — Tue, 06 Sep 2022 22:09:22 GMT

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;