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
- /
- General Programming
- /
- Question on averages in proc tabulate

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-03-2014 04:36 PM

Hi,

I am working on using proc tabulate to create a table that looks similar to this example;

Location | Period | AvgExamTime | AvgCheckinTime | AvgDischargeTime |
---|---|---|---|---|

Location1 | Month1 | |||

Month2 | ||||

Location2 | Month1 | |||

Month2 | ||||

Location3 | Month1 | |||

Month2 | ||||

All Locations | Month1 | |||

Month2 |

My actual data has more row and column variables. The data example shows people identified in each period and location, it also shows the total number of minutes each person spent during check in,during examination and during checkout. So far I tried splitting the data by time periods (2 data sets for month1 and month2), then calculated the average values in a macro.

Here is a sample after sorting the data by location;

Data Time1;

set test1;

by location;

retain sum count;

if first.location then do;

sum=0; count=0;

end;

sum=sum+examtime; /*this will change to represent other column variables in macro not shown here*/

count=count+1;

Avg1=sum/count; /*macro will also generate Avg2 and Avg3*/

if last.location then output;

run;

data all;

set time1 time2;

run;

proc tabulate data=all;

class location period avg1 avg2 avg3;

Tables(location=' ' * period= ' ') ,(Avg1='AvgExamTime' Avg2='AvgCheckinTime' Avg3='AvgDischargeTime');

run;

I noticed that the average values will not be computed in the tables, just the number of observations for the average values. Any suggestions??

Thanks

Accepted Solutions

Solution

01-03-2014
06:14 PM

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

Posted in reply to Sara3

01-03-2014 06:13 PM

You typically don't pre-calculate statistics for proc tabulate, but do for proc report.

I believe proc tabulate can accomplish what you need from the raw data, and use an all key word to get the totals you want.

proc tabulate data=test1;

class location period ;

var examtime checkintime dischargetime;

Tables(location=' ' all = 'All Locations')* period= ' ' , examtime='AvgExamTime' *mean checkintime='AvgCheckinTime'*mean DischargeTime='AvgDischargeTime'*mean;

run;

All Replies

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

Posted in reply to Sara3

01-03-2014 04:44 PM

To calculate anything other than a count the variable as to be declared as a VAR variable not CLASS. Class variables can only generate counts or percents of counts.

You also have to request statistics for the variables.

proc tabulate data=all;

class location period ;

**VAR **avg1 avg2 avg3;

Tables (location=' ' * period= ' ') ,

(Avg1='AvgExamTime' Avg2='AvgCheckinTime' Avg3='AvgDischargeTime') *** mean**;

run;

Solution

01-03-2014
06:14 PM

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

Posted in reply to Sara3

01-03-2014 06:13 PM

You typically don't pre-calculate statistics for proc tabulate, but do for proc report.

I believe proc tabulate can accomplish what you need from the raw data, and use an all key word to get the totals you want.

proc tabulate data=test1;

class location period ;

var examtime checkintime dischargetime;

Tables(location=' ' all = 'All Locations')* period= ' ' , examtime='AvgExamTime' *mean checkintime='AvgCheckinTime'*mean DischargeTime='AvgDischargeTime'*mean;

run;

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

Posted in reply to Reeza

01-03-2014 06:57 PM

Hi:

Actually, except for Kurtosis and Skewness and some of the special TABULATE percentage statistics, PROC REPORT can calculate almost the same set of statistics as TABULATE. So while you might need to pre-calculate some statistics for TABULATE, it is not a given that you ALWAYS need to pre-calculate statistics for PROC REPORT. (Base SAS(R) 9.4 Procedures Guide, Second Edition)

cynthia

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

Posted in reply to Reeza

01-06-2014 09:46 AM

Thanks all. Reeza, I applied your suggestion and it worked perfectly!! Thank you.