SAS: tabulate with a row for every metric*class combination

Question

I'm trying to run a PROC TABULATE to get means for various variables split across a class variable (called brand). I'd like the output to come out so that it's one row for each combination of metric & brand. This is so I can copy the output into Excel and create a pivot table to share with users. The table would be 1 column X however many rows brand*metric generates.

The problem is that SAS will generate nested cells with merged tables, so I can't easily copy and paste the output -- I'd have to manually clean a bunch of cells.

I'm not very knowledgeable in SAS, so any help is appreciated. Thanks!

Answer 1

I think you can just use PROC UNIVARIATE, if I understand what you want. Let's use SASHELP.CARS as our example data. We can treat MODEL as your BRAND and get the mean of the MPG variables.

proc univariate noprint
  data=sashelp.cars(where=(make=:'H'))
  outtable=stats (keep=make _var_ _label_ _mean_)
;
  class  make ;
  var mpg: ;
run;

Results

Obs     Make         _VAR_          _LABEL_        _MEAN_
 1     Honda      MPG_City       MPG (City)       27.8235
 2     Hummer     MPG_City       MPG (City)       10.0000
 3     Hyundai    MPG_City       MPG (City)       23.0000
 4     Honda      MPG_Highway    MPG (Highway)    34.0000
 5     Hummer     MPG_Highway    MPG (Highway)    12.0000
 6     Hyundai    MPG_Highway    MPG (Highway)    29.9167

Note: PROC UNIVARIATE will not allow more than two class variables. If you have more than two class variables you can just sort the source data and use a BY statement instead of the CLASS statement.

If you need multiple combinations of class variables (MAKE MAKE*DRIVETRAIN etc) then you probably want to use PROC SUMMARY instead. But you will then need to transpose the data set to convert the variables into rows instead of columns. Plus PROC SUMMARY has many fewer statistics available in this simple output format than are available in the OUTTABLE= results from PROC UNIVARIATE.

%let class_list=make drivetrain ;
%let class_types=() make make*drivetrain ;
%let varlist = mpg: ;

proc summary chartype
  data=sashelp.cars(where=(make=:'H'))
;
  class &class_list ;
  types &class_types ;
  var &varlist ;
  output out=stats(where=(_stat_='MEAN')) ;
run;
proc transpose data=stats out=want;
  by _type_ &class_list ;
  var &varlist ;
  id _stat_;
run;
proc print; run;

Result

                            Drive
Obs    _TYPE_     Make      Train      _NAME_          _LABEL_         MEAN

  1      00                          MPG_City       MPG (City)       25.3000
  2      00                          MPG_Highway    MPG (Highway)    31.6333
  3      10      Honda               MPG_City       MPG (City)       27.8235
  4      10      Honda               MPG_Highway    MPG (Highway)    34.0000
  5      10      Hummer              MPG_City       MPG (City)       10.0000
  6      10      Hummer              MPG_Highway    MPG (Highway)    12.0000
  7      10      Hyundai             MPG_City       MPG (City)       23.0000
  8      10      Hyundai             MPG_Highway    MPG (Highway)    29.9167
  9      11      Honda      All      MPG_City       MPG (City)       19.6667
 10      11      Honda      All      MPG_Highway    MPG (Highway)    23.6667
 11      11      Honda      Front    MPG_City       MPG (City)       30.3077
 12      11      Honda      Front    MPG_Highway    MPG (Highway)    37.0769
 13      11      Honda      Rear     MPG_City       MPG (City)       20.0000
 14      11      Honda      Rear     MPG_Highway    MPG (Highway)    25.0000
 15      11      Hummer     All      MPG_City       MPG (City)       10.0000
 16      11      Hummer     All      MPG_Highway    MPG (Highway)    12.0000
 17      11      Hyundai    Front    MPG_City       MPG (City)       23.0000
 18      11      Hyundai    Front    MPG_Highway    MPG (Highway)    29.9167

Answer 2

Your best bet is to use ods output to get this into a table, then use proc transpose to make it how you want it. PROC TABULATE is going to make something that looks nice, not necessarily something useful for your specific purpose.

ods output table=tab_out;
proc tabulate data=sashelp.cars;
  class make;
  var mpg_city mpg_highway;
  tables make*(mpg_city mpg_highway),mean;
run;
ods output close;

proc transpose data=tab_out out=tab_trans;
  by make;
  var mpg_:;
run;

With PROC MEANS it can skip the transpose if you are 9.3+ and have STACKODSOUTPUT option available to you; without that (in older versions) just remove that option and then do the transpose:

ods output summary=means_out;
proc means data=sashelp.cars mean stackodsoutput;
  class make;
  var mpg_city mpg_highway;
run;
ods output close;