proc tabulate: calculating within table from tabulate result?

[ucdcrush]

Hi all, I am using the following code which generates a table showing the mean of variables oldrsd and newrsd (which are either 1 or 0 in the dataset), by year.

-----------------------

proc tabulate data=geo.newperson;

class dx_year;

var oldrsd newrsd;

table dx_year, (oldrsd newrsd)*(mean='Pct'*F=8.2);

run;

-----------------------

What I'd like to do is to have a third column which would show the difference between the means as calculated by proc tabulate.

e.g the current table shows

             oldrsd     newrsd

2000       .50            .70

2001       .40            .90

but would like it to show

             oldrsd     newrsd      diff

2000       .50            .70        .20

2001       .40            .90        .50

---------------------------------

Is this possible to do, within proc tabulate? Or what would be an efficient way to do this?

Thanks.

[Reeza]

You can take the output table from proc tabulate and add that column in and then report it.

I don't know if there's a mathematical shortcut for the difference. 

[Astounding · Accepted solution]

If it's easier, you could always compute DIFF on your input data set:

diff = newrsd - oldrsd;

Then let PROC TABULATE compute its mean as well as the mean of the other variables.  The mean value would be the same as long as there are no missing values for the incoming variables.

[ucdcrush]

Thanks Reeza and Astounding. Calculating it before seems the quickest way since one can simply add diff in as a variable to have the mean computed along with newrsd and oldrsd in the proc tabulate, without having to do any wrangling on an output table.

[Cynthia_sas]

Hi:

  OR, as much as I love TABULATE, you could do it all in one pass through the data with PROC REPORT.

cynthia

ods html file='c:\temp\calcdiff.html';

proc report data=sashelp.shoes nowd;

  column region inventory sales diff;

  define region/ group;

  define sales / mean;

  define inventory / mean;

  define diff / computed f=dollar12.;

  compute diff;

    diff =  inventory.mean - sales.mean;

  endcomp;

  rbreak after / summarize;

run;

ods html close;

[ucdcrush]

Hi Cynthia, that is great. I didn't know proc report was so straightforward, I will probably use it more often than tabulate now. Thanks!

[Cynthia_sas]

Hi,

I call TABULATE and REPORT the "dream team" of SAS reporting. TABULATE is the slicer and dicer and power 3 dimensional --( nestings in every dimension) report procedure and REPORT is the "Swiss Army Knife" with the fold out tools report procedure -- PROC REPORT does detail reports, like PRINT; it can calculate summary statistics like MEANS and TABULATE; it can do crosstabs; it can calculate new columns from report items in the COLUMN statement (such as DIFF); it can have customized break lines. They each have a purpose, but when you talk about making 2 passes through the data to get TABULATE to have a column that needs to be computed, that's the time to look at REPORT.

cynthia