BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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?

Capture.PNG

 

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.

--
Paige Miller
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1588943346450.png

  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

View solution in original post

8 REPLIES 8
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1588943346450.png

  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
Diamond | Level 26

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.

--
Paige Miller
bobpep212
Quartz | Level 8

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
Super User

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
Diamond | Level 26

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?

--
Paige Miller
Cynthia_sas
SAS Super FREQ

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
Diamond | Level 26

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.

 

 

--
Paige Miller
Ksharp
Super User
Use this code
proc report data=sashelp.class nowd out=check ;

You are going to find a variable named _BREAK_ .

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2122 views
  • 6 likes
  • 4 in conversation