PROC REPORT, compare to value in first row

[PaigeMiller]

Suppose I have a SAS data set where the first row is an overall average (see data set named CLASS in the example below). I then want to use PROC REPORT to display all the rows and compare a variable to the overall average in the first row and have PROC REPORT highlight the row if the variable is greater than the average. How can I do this?

Example:

proc summary data=sashelp.class;
	var height weight;
	output out=class_means mean=;
run;
data class;
	set class_means(keep=height weight) sashelp.class;
run;
proc report data=class;
    columns name sex age height weight;
	define name / display missing;
	define sex / display missing;
	define age / display missing;
	define height/display format=5.1;
	define weight/display format=5.1;
	compute name;
	    if missing(name) then name='Avg';
	endcompute;
run;

Desired output, the heights that are greater than the average are shown with yellow background, how can I get PROC REPORT to do this?

I know how to turn individual cells to have a certain background color, I don't know how to write an IF statement in the PROC REPORT compute block that compares the value in the cell to the value in the first row.

[Cynthia_sas · Accepted solution]

Hi:

  You were very close. You just needed a few things. Just as you changed the value of the NAME column to be Avg, you can use that value in a COMPUTE block for WEIGHT to grab and save the values on that row. Then you can use the temporary helper variables in IF statements to test:

  If you don't want to use helper/temporary variables in PROC REPORT, then an alternate approach is to massage the data in your DATA step program to make your helper variables and use NOPRINT in the PROC REPORT step.

proc summary data=sashelp.class;
	var height weight;
	output out=class_means mean=holdavg_h holdavg_w;
run;
 
data class;
    if _n_ = 1 then set class_means(keep=holdavg_h holdavg_w);
	retain holdavg_h holdavg_w;
	set sashelp.class;
	ord = _n_;
	if _n_ = 1 then name='Avg';
run;

proc report data=class;
    columns ord name sex age holdavg_h holdavg_w height weight;
	define ord / order noprint;
	define name / display missing;
	define sex / display missing;
	define age / display missing;
	define holdavg_h/display format=5.1 noprint;
	define holdavg_w/display format=5.1 noprint;
	define height/display format=5.1;
	define weight/display format=5.1;
	compute weight;
	    if height gt holdavg_h then
	       call define('height','style','style={background=yellow}');
	    if weight lt holdavg_w then
	       call define(_col_,'style','style={background=lightblue}');
	endcomp;
run;

Either approach will work. Just depends on your preference.

Cynthia

[PaigeMiller]

Thank you @Cynthia_sas , I have done the second method, but it bothered me to have to create a new variable which has a constant value in my data step just to do this comparison. I think in the future, I will use your first method.

[bobpep212]

After your output of class_means, can you create a macro variable for the mean you want to compare it to. Then create a format with proc format using that macro variable as a cutoff in your value statement. Similar to the pattern on page 9-10 of this https://support.sas.com/resources/papers/proceedings13/366-2013.pdf. 

[Ksharp]

One shot .

proc report data=sashelp.class nowd;
columns name sex age height weight;
define name /display;
define sex/display;
define age/display;
define height/analysis mean format=12.1;
define weight/analysis mean format=12.1;
compute before;
name='AVG:'; avg_height=height.mean;
endcomp;
compute height;
if height.mean>avg_height and missing(_break_) 
then call define(_col_,'style','style={background=yellow}');
endcomp;
rbreak before /summarize ;
run;

[PaigeMiller]

Well that's impressive taht you can do this all in one PROC. There are soooo many features in PROC REPORT, I'm surprised SAS had the time to program them all.

However, one command I do not understand:

if height.mean>avg_height and missing(_break_) 
then call define(_col_,'style','style={background=yellow}');

I would think you want

if height > avg_height and missing(_break_) then ...

but this doesn't work. Could you explain that part?

[Cynthia_sas]

Hi:
When you use a statistic in a DEFINE statement for a numeric variable, such as:
define var1 / sum;
define var2 / analysis sum;
define var3 / mean;
define var4 / analysis mean;
Then you need to use the "compound name" of the variable in a COMPUTE block. That compound name is variable.statistic -- and for the above 4 define statements would be:
var1.sum
var2.sum
var3.mean
var4.mean

This compound name requirement does NOT apply to numeric variables used as GROUP, ORDER, DISPLAY or aliased items, but using DISPLAY for a numeric item also turns off summarizing at a break. I frequently use DISPLAY for variables like AGE or DATE, but always use SUM for variables like AMOUNT and SALES (as examples) because I usually want to summarize variables like AMOUNT and SALES, but NOT summarize variables like AGE or DATE.

Cynthia

[PaigeMiller]

So in the example from @Ksharp he is using define height/analysis mean; which obtains the "mean" of a single value, which is that single value. What I used define height/display; doesn't compute a mean, and isn't going to work in the example from @Ksharp . I guess I need to let this information sink in.

[Ksharp]

Use this code
proc report data=sashelp.class nowd out=check ;

You are going to find a variable named _BREAK_ .