turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- proc tabulate: calculating within table from tabul...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2013 11:58 AM

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.

Accepted Solutions

Solution

03-20-2013
01:50 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ucdcrush

03-20-2013 01:50 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ucdcrush

03-20-2013 12:21 PM

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.

Solution

03-20-2013
01:50 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ucdcrush

03-20-2013 01:50 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-20-2013 02:03 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ucdcrush

03-20-2013 03:23 PM

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;**

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

03-20-2013 04:18 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ucdcrush

03-21-2013 11:51 AM

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