SAS: replication of a PROC MEANS result to PROC TABULATE

Question

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

Here is an example of 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 calculated the statistics using PROC MEANS (for example, below), but the value for the SUM statistics for the "margin" variable does not mean anything: I would like to suppress this value. Therefore, I am trying to reproduce this table using PROC TABULATE in order to have more control over the output, but have not yet succeeded.

  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 with this.

Answer 1

Basically, you cannot create this type of output as part of the default TABULATE function; in essence, you are querying for two different table definitions. All you do with SAS syntax will basically be to add more dimensions to the table, but that will not solve your main problem.

This code can be used to get the required tables, but they are 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 several ODS hacking guides to do what you want (namely, creating “frozen tables” where several child tables are grouped into one table. Look here . If you find “SAS Stack Tables” on Google, you will find more examples.

I did this in HTML by creating a new set of tags - basically a special ODS clause that removes spaces between tables, etc. Unfortunately, I do not have the code that I used more; I moved to R to do automatic reporting.