Proc tabulate for simple statistics (corrected)

Ken Beath, of Macquarie University, commented on an earlier entry that the best way to generate summary statistics is using proc tabulate. While the best tools might differ, depending on the purpose, we wanted to share Ken's code demonstrating how to replicate the R mosaic package tables using proc tabulate.

SAS

Ken's fully annotated code is appended below; we highlight the key syntax elements here. Reading in the data is shown in many examples.

proc tabulate data=help;
 class substance;
 var cesd;
 table (substance all),cesd*(n nmiss mean median);
run;

The class and var statements serve their usual purpose of identifying the categorical and analysis variables. The table statement does the work of the procedure, specifying a table with rows (requested before the comma) for each level of substance and overall, with columns (requested after the the *) to include the listed statistics for the analysis variable cesd. The resulting table is shown below.

    ------------------------------------------------------------------------
    |                  |                       cesd                        |
    |                  |---------------------------------------------------|
    |                  |     N      |   NMiss    |    Mean    |   Median   |
    |------------------+------------+------------+------------+------------|
    |substance         |            |            |            |            |
    |------------------|            |            |            |            |
    |alcohol           |      177.00|        0.00|       34.37|       36.00|
    |------------------+------------+------------+------------+------------|
    |cocaine           |      152.00|        0.00|       29.42|       30.00|
    |------------------+------------+------------+------------+------------|
    |heroin            |      124.00|        0.00|       34.87|       35.00|
    |------------------+------------+------------+------------+------------|
    |All               |      453.00|        0.00|       32.85|       34.00|
    ------------------------------------------------------------------------

Below we show Ken's code, complete with his helpful annotations. Note his use of formats. We're not fond of formats, but for presentation, as opposed to analysis, they can be very useful.

(Note: a prior version of this entry inexplicably referred to proc report, rather than proc tabulate.)

PROC IMPORT OUT= help
  DATAFILE= "C:\Users\kbeath\Documents\tabulate\help.csv" 
  DBMS=CSV REPLACE;
  GETNAMES=YES;
  DATAROW=2; 
RUN;

/* missing option create a category missing for each categorical 
    variable, always a good idea;
  the table statement specifies row then column;
  so for this example we have substance defining the rows, and 
    cesd statistics the columns */

proc tabulate data=help missing;
 class substance;
 var cesd;
 table substance,cesd*(mean n nmiss);
run;

/* formchar specifies the characters used to form the borders 
      - we set them all to blank to have no borders;
   mean*f=8.2 specifies that the mean is formatted using 
      an 8.2 format, etc*/

proc tabulate data=help missing formchar='           ';
 class substance;
 var cesd;
 table substance,cesd*(mean*f=8.2 n*f=8. nmiss*f=8.);
run;


/* substance="Substance" causes change in label to Substance */

proc format;
 value $subf "alcohol"="Alcohol" 
          "cocaine"="Cocaine" "heroin"="Heroin";

proc tabulate data=help missing formchar='           ';
 class substance;
 var cesd;
 format substance $subf.;
 table substance="Substance",
            cesd="CESD"*(mean*f=8.2 n*f=8. nmiss*f=8.);
run;

/* all the statistics. I've changed the format to 7.2 
     so they all fit on a line */

proc tabulate data=help missing formchar='           ';
 class substance;
 var cesd;
 format substance $subf.;
 table substance="Substance",
            cesd="CESD"*(n*f=8. nmiss*f=8. 
            (mean std min q1 median q3 max)*f=7.2);
run;

/* add a line for all */

proc tabulate data=help missing formchar='           ';
 class substance;
 var cesd;
 format substance $subf.;
 table (substance="Substance" all),
            cesd="CESD"*(n*f=8. nmiss*f=8. 
            (mean std min q1 median q3 max)*f=7.2);
run;

/* to show how easy it is, further subdivide 
   by racial group */

proc tabulate data=help missing formchar='           ';
 class substance racegrp;
 var cesd;
 format substance $subf.;
 table (racegrp all)*(substance="Substance" all),
            cesd="CESD"*(n*f=8. nmiss*f=8. 
            (mean std min q1 median q3 max)*f=7.2);
run;