PROC REPORT - subset WHERE or IF THEN within a compute block to calculate statistics

[DougHold]

I am using the SAShelp cars data. Here I am creating a report of the mean MSRP subset according to Origin and DriveTrain BY whether the cars have horsepower greater than 200 or less than equal to 200.

The following gives me almost what I want, but I would like it to appear together in one table rather than two separate tables.

DATA HP_Cars;
	SET sashelp.cars;
	IF Horsepower > 200 THEN HP200 = 1;
	                    ELSE HP200 = 0;
RUN;

PROC SORT DATA=Hp_cars;
	BY HP200;
RUN;

ODS PDF;
PROC REPORT DATA = HP_Cars    OUT=Cars_report;
	Title "Cars Report mean MSRP";
	    BY HP200; 
	COLUMN 
        Origin
        DriveTrain
		MSRP = mean_MSRP
		;    
	DEFINE Origin        / group;
	DEFINE DriveTrain    / group;
	DEFINE mean_MSRP     / analysis mean "mean_MSRP";

	BREAK AFTER Origin / summarize style=[background=graydd];
		COMPUTE AFTER Origin;
			DriveTrain='Any';
		ENDCOMP;

	RBREAK AFTER / summarize style=[background=lightblue];
		COMPUTE AFTER;
			Origin='Any';
			DriveTrain='Any';
		ENDCOMP;
RUN;
ODS PDF CLOSE;

Alternatively to the above, is it possible to subset (i.e. using WHERE or IF THEN) within a compute block to calculate the means? The following doesn't actually work, but for the purpose of learning, I would like to know if it's even possible to do something like this:

ODS PDF;
PROC REPORT DATA = HP_Cars    OUT=Cars_report;
	Title "Cars Report mean MSRP";
    COLUMN 
        Origin
        DriveTrain
		MSRP = mean_MSRP
		HP200
        mean_MSRP_HP200plus
		mean_MSRP_HP200less
		;    
	DEFINE Origin                / group;
	DEFINE DriveTrain            / group;
	DEFINE mean_MSRP             / analysis mean noprint;
	DEFINE HP200                 / computed noprint;
	DEFINE mean_MSRP_HP200plus   / computed "mean_MSRP HP>200";
    DEFINE mean_MSRP_HP200less   / computed "mean_MSRP HP<=200";

    COMPUTE mean_MSRP_HP200plus;
	     IF HP200=1 THEN mean_MSRP_HP200plus = MEAN(MSRP);     *calculate mean MSRP for HP > 200; 
    ENDCOMP;

	COMPUTE mean_MSRP_HP200less;
	     IF HP200=0 THEN mean_MSRP_HP200less = MEAN(MSRP);     *calculate mean MSRP for HP <= 200; 
    ENDCOMP;

	BREAK AFTER Origin / summarize style=[background=graydd];
		COMPUTE AFTER Origin;
			DriveTrain='Any';
		ENDCOMP;

	RBREAK AFTER / summarize style=[background=lightblue];
		COMPUTE AFTER;
			Origin='Any';
			DriveTrain='Any';
		ENDCOMP;
RUN;
ODS PDF CLOSE;

What I want is this:

[Kurt_Bremser]

Apply a format to HP200 which displays the texts, and use HP200 as an ACROSS variable:

data HP_Cars;
set sashelp.cars;
hp200 = Horsepower > 200;
run;

proc format;
value hp200_ /* underline is needed because a format name must not end with a digit */
  0 = "mean_MSRP HP<=200"
  1 = "mean_MSRP HP>200"
;
run;

proc report data=hp_cars;
column origin drivetrain msrp,hp200;
define origin / group;
define drivetrain /group;
define msrp / "" analysis mean;
define hp200 / "" across format=hp200_.;
run;

[Ksharp · Accepted solution]

/*
As Kurt point out ,Adding a format and a ACROSS variable
*/

proc format;
value hp200_ /* underline is needed because a format name must not end with a digit */
  0 = "mean_MSRP HP<=200"
  1 = "mean_MSRP HP>200"
;
run;

DATA HP_Cars;
 SET sashelp.cars;
 IF Horsepower > 200 THEN HP200 = 1;
                     ELSE HP200 = 0;
RUN;

PROC SORT DATA=Hp_cars;
 BY HP200;
RUN;


PROC REPORT DATA = HP_Cars   nowd OUT=Cars_report;
 Title "Cars Report mean MSRP";
 COLUMN 
        Origin
        DriveTrain
  MSRP,HP200 
  ;    
    define HP200 /across format=hp200_. "";
 DEFINE Origin        / group;
 DEFINE DriveTrain    / group;
 DEFINE MSRP     / analysis mean "";

 BREAK AFTER Origin / summarize style=[background=graydd];
  COMPUTE AFTER Origin;
   DriveTrain='Any';
  ENDCOMP;

 RBREAK AFTER / summarize style=[background=lightblue];
  COMPUTE AFTER ;
   Origin='Any';
   DriveTrain='Any';
  ENDCOMP;

RUN;

[DougHold]

Thank you Kurt and Ksharp, this works.