SAS: Replicate PROC MEANS output in PROC TABULATE

Question

I would like to replicate the output of PROC MEANS using PROC TABULATE. The reason for this is that I would like to have a profit percentage (or margin) as one of the variables in the PROC MEANS output, but would like to suppress the calculation for one or more of the statistics i.e. there will be a '-' or similar in the 'margin' row under 'N' and 'SUM.

Here is the sample data:

    data have;
       input username $  betdate : datetime. stake winnings;
       dateOnly = datepart(betdate) ;
       format betdate DATETIME.;
       format dateOnly ddmmyy8.;
       datalines; 
        player1 12NOV2008:12:04:01 90 -90 
        player1 04NOV2008:09:03:44 100 40 
        player2 07NOV2008:14:03:33 120 -120 
        player1 05NOV2008:09:00:00 50 15 
        player1 05NOV2008:09:05:00 30 5 
        player1 05NOV2008:09:00:05 20 10 
        player2 09NOV2008:10:05:10 10 -10 
        player2 15NOV2008:15:05:33 35 -35 
        player1 15NOV2008:15:05:33 35 15 
        player1 15NOV2008:15:05:33 35 15 
    run;

    data want;
        set have;
        retain margin;
        margin = (winnings) / stake;
    PROC PRINT; RUN;

I have been calculating statistics with PROC MEANS (like below), but the value for the SUM statistics for the 'margin' variable means nothing: I would like to suppress this value. I have therefore been attempting to replicate this table using PROC TABULATE to have more control of the output, but have been unsuccessful so far.

    proc means data=want N sum mean median stddev min max maxdec=2 order=freq STACKODS;
        var stake winnings margin;
    run;

    proc tabulate data=want;
        var stake winnings margin;
        table stake * (N Sum mean Median StdDev Min Max);
    run;

I would appreciate any help on this.

TARehman TARehman · Accepted Answer · 2013-08-09T23:24:21

In principle, you can't create this type of output as a default part of the TABULATE function; in essence, you are asking for two different table definitions. Anything you do with the SAS syntax will basically amount to adding more dimensions to the table, but it won't fix your core problem.

You can use this code to get the tables you want, but they're still different tables:

PROC TABULATE DATA=want NOSEPS;
    VAR stake winnings margin;
    TABLE (stake winnings),(N SUM MEAN MEDIAN STDDEV MIN MAX);
    TABLE (margin),(N MEAN MEDIAN STDDEV MIN MAX);
RUN;

There are some guides out there on hacking ODS to do what you want (namely, create "stacked tables" where several child tables are assembled into a single table. Check out here for an example. If you Google "SAS stack tables" you'll find more examples.

I've done this in HTML by creating a new tagset - basically, a special ODS destination that removes spaces between tables, etc. I don't have the code that I used anymore, unfortunately; I moved to R to do automated reporting.

SAS: Replicate PROC MEANS output in PROC TABULATE

1 Answers