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:
/*
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;
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;
/*
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;
Thank you Kurt and Ksharp, this works.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.