topic proc tabulate: calculating within table from tabulate result? in SAS Procedures

proc tabulate: calculating within table from tabulate result?

ucdcrush — Wed, 20 Mar 2013 15:58:37 GMT

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.

Re: proc tabulate: calculating within table from tabulate result?

Reeza — Wed, 20 Mar 2013 16:21:48 GMT

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.

Re: proc tabulate: calculating within table from tabulate result?

Astounding — Wed, 20 Mar 2013 17:50:43 GMT

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.

Re: proc tabulate: calculating within table from tabulate result?

ucdcrush — Wed, 20 Mar 2013 18:03:30 GMT

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.

Re: proc tabulate: calculating within table from tabulate result?

Cynthia_sas — Wed, 20 Mar 2013 19:23:14 GMT

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;

Re: proc tabulate: calculating within table from tabulate result?

ucdcrush — Wed, 20 Mar 2013 20:18:46 GMT

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!

Re: proc tabulate: calculating within table from tabulate result?

Cynthia_sas — Thu, 21 Mar 2013 15:51:26 GMT

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