Customizing output from PROC MEANS

Customizing the output data set created using the OUTPUT statement

When you request statistics on the PROC MEANS statement, the default printed output creates a nice table with the analysis variable names in the left-most column and the statistics forming the additional columns.  Even if you create an output data set with no statistics listed on the OUTPUT statement, the default statistics, N, MIN, MAX, MEAN, and STD, are output in a nice table format as values of the _STAT_ variable and the analysis variables form the other columns.  Once you start requesting specific statistics on the OUTPUT statement, the format of the data set changes.  A wide output data set is the result with only one observation and a variable for each analysis variable-statistic combination.  If you use a BY or CLASS statement, you will get multiple observations for each unique value of the BY or CLASS variable combinations, but the analysis variable structure is the same.  The structure of this data set can be hard to view.  It would be nice if the output data set could maintain the format of the printed output or the default output data set.  You can do that with a few simple code modifications using PROC TRANSPOSE and DATA Step logic.

If you want the output data set to look like the default printed output, use the steps below.

/* create a sample data set */
data test;
input x1 x2 x3;
cards;
1 2 3
4 5 6
7 8 9
;
run;

/* Request statistics on the OUTPUT statement. */
/* Use the AUTONAME option to create variable names in the form: */
/* variable_statistic */

proc means noprint;
var _numeric_;
output out=new(drop=_type_ _freq_) mean= median= std= q1= q3= / autoname;
run;

/* Transpose the data set so that each statistic becomes an observation. */
proc transpose data=new out=out;
run;

/* Create new variables that contain the statistic name and the */
/* original variable name. */

data out1;
set out;
varname=scan(_name_,1,'_');
stat=scan(_name_,2,'_');
drop _name_;
run;

proc sort data=out1;
by varname;
run;

/* Transpose the data to get one observation for each */
/* original variable name and one variable for each */
/* statistic. This mimics the default printed output. */

proc transpose data=out1 out=out2(drop=_name_);
by varname;
id stat;
var col1;
run;

proc print data=out2;
title 'Looks like default printed output';
run;

Looks like default printed output

varname | Mean | Median | StdDev | Q1 | Q3
x1 | 4 | 4 | 3 | 1 | 7
x2 | 5 | 5 | 3 | 2 | 8
x3 | 6 | 6 | 3 | 3 | 9

If you want the output data set to look like the default output data set, use the steps below in place of the last three steps above.

proc sort data=out1;
by stat;
run;

/* Transpose the data to get one observation for each */
/* statistic name and one variable for each */
/* original variable. This mimics the default */
/* output data set. */

proc transpose data=out1 out=out3(drop=_name_);
by stat;
id varname;
var col1;
run;

proc print data=out3;
title 'Looks like default output data set';
run;

Looks like default output data set

stat | x1 | x2 | x3
Mean | 4 | 5 | 6
Median | 4 | 5 | 6
Q1 | 1 | 2 | 3
Q3 | 7 | 8 | 9
StdDev | 3 | 3 | 3

If your variable names contain underscores (_) in the name, replace the DATA OUT1 step above with the following DATA OUT1 step:

/* Depending on your variable name, you may need to increase */
/* the length $11 assignment below. */

data out1;
set out;
length varname $11;
lenvar=length(_name_);
lenstat=index(reverse(trim(_name_)),'_')-1;
varname=substr(_name_,1,(lenvar-lenstat)-1);
stat=scan(_name_,-1,'_');
drop _name_ lenvar lenstat;
run;

Customizing the output using PROC TEMPLATE

For the default printed output from PROC MEANS, in the past we said that the only option for formatting the statistics was with the MAXDEC= option which controls the number of decimal places.  We are no longer limited to this level of formatting since we can use PROC TEMPLATE.  We can format the statistics in the printed output by modifying the Base.Summary table template.

/* create sample data */
data test;
input amount ;
cards;
1000
12345
8500
;
run;

/* Modify the base.summary table template using */
/* EDIT statements. */
ods path(prepend) work.templat(update);
proc template;
edit base.summary;
edit mean;
format=dollar12.2;
end;
edit sum;
format=dollar12.;
end;
end;
run;

proc means data=test n mean sum;
var amount;
run;

/* restore default template */
proc template;
delete base.summary;
run;

Custom format for printed output

The MEANS Procedure

Analysis Variable : amount
N | Mean | Sum
3 | $7,281.67 | $21,845

Customizing the output data using STACKODSOUTPUT

In SAS 9.3, a new option was added that only affects the output data set created using the ODS OUTPUT statement.  This option is STACKODSOUTPUT (alias: STACKODS) and it is placed on the PROC MEANS statement.  This option allows the data set to resemble the default printed output.

/* Use the STACKODSOUTPUT option on the PROC MEANS statement */
/* and use the ODS OUTPUT statement to create output data set. */

ods output summary=with_stackods(drop=_control_);
proc means data=sashelp.class stackodsoutput sum mean std nway;
class sex;
var height weight;
run;

proc print data=with_stackods noobs;
title 'Output data set with STACKODSOUTPUT';
run;

Output data set with STACKODSOUTPUT

Sex | NObs | Variable | Sum | Mean | StdDev
F | 9 | Height | 545.300000 | 60.588889 | 5.018328
F | 9 | Weight | 811.000000 | 90.111111 | 19.383914
M | 10 | Height | 639.100000 | 63.910000 | 4.937937
M | 10 | Weight | 1089.500000 | 108.950000 | 22.727186

If you are not satisfied with the output from PROC MEANS, either the default printed output or the output data set, you are just a few steps away from reshaping it into the format you desire.
WANT MORE GREAT INSIGHTS MONTHLY? | SUBSCRIBE TO THE SAS TECH REPORT
Customizing output from PROC MEANS was published on SAS Users.