Solved
New Contributor
Posts: 2

# Question on averages in proc tabulate

Hi,

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

LocationPeriodAvgExamTimeAvgCheckinTimeAvgDischargeTime
Location1Month1
Month2
Location2Month1
Month2
Location3Month1
Month2
All LocationsMonth1
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
Super User
Posts: 23,776

## Re: Question on averages in proc tabulate

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
Super User
Posts: 13,583

## Re: Question on averages in proc tabulate

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
Super User
Posts: 23,776

## Re: Question on averages in proc tabulate

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;

SAS Super FREQ
Posts: 9,371

## Re: Question on averages in proc tabulate

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

New Contributor
Posts: 2

## Re: Question on averages in proc tabulate

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

🔒 This topic is solved and locked.