## Calculate field within Tabulate

Frequent Contributor
Posts: 129

# Calculate field within Tabulate

Hi All,

I'm trying to use Proc Tabulate to create a table for me. I need to use two fields from the data to calculate the Average Speed of Answer.

Sample of Data

 WEEK_END_DT EVENT_DT MBL CLUSTER CALLS_ANSWERED_CNT TOTAL_ANSWER_TM ASA 4/12/2016 28/11/2016 MBL DSC 7530 12321475 27:16 4/12/2016 28/11/2016 MBL EMS 7969 14496946 30:19 4/12/2016 28/11/2016 MBL DFAP 21769 20910688 16:01 4/12/2016 28/11/2016 MBL OA 5217 6588138 21:03 4/12/2016 28/11/2016 MBL PST 3360 9125172 45:16 4/12/2016 28/11/2016 MBL YAS 4724 9069548 32:00 4/12/2016 28/11/2016 Oth ABS 618 594013 16:01 4/12/2016 28/11/2016 Oth CMC 3719 3004070 13:28 4/12/2016 28/11/2016 Oth CCP 18 3183 02:57

The above allows me to get the columns right but how do I get the row and Column total to be claculated out to show the right answer.

Can I do the calculations in the Proc tabulate and not show the Total_Answer_TM and Calls_Answered_CNT  fileds in the ouput? and not have to do the proc summary and new data step?

``````Data Report.SSW_ASA;
Set Cartel.preagent_telephony;
Keep Week_end_dt EVENT_DT PROGRAMME_NM CLUSTER CALLS_ANSWERED_CNT TOTAL_ANSWER_TM MBL
;
Where week_end_dt = '04Dec2016.'d
and CALL_TYPE = 'External'
and PROGRAMME_NM IN ("SSW");

IF Cluster_ID in ('1' , '6', '9', '13', '18', '27') and Queue_Type IN (CHOPS', 'PST') THEN
MBL = 'MBL';
ELSE MBL = 'Other';
Run;

Proc Sort data=Report.SSW_ASA;
By Week_end_dt EVENT_DT PROGRAMME_NM MBL CLUSTER;
Run;

Proc Summary data=Report.SSW_ASA SUM NOPRINT;
by Week_end_dt EVENT_DT PROGRAMME_NM MBL CLUSTER;
Output out=Report.SSW_ASA_SUM (DROP=_TYPE_ _FREQ_) SUM=;
Run;

Data Work.SSW_ASA_2;
Set Work.SSW_ASA_SUM;
Format ASA mmss.;
Run;

proc tabulate data=Work.SSW_ASA_2 format=mmss.;
class event_dt MBL cluster;
var ASA;

table
MBL*cluster=" " All = "Daily Totals"
, event_dt=" "*ASA=" " All="Weekly Total"*ASA=" "
/ box="Cluster"
;
keylabel sum= " ";
Title "Average Speed of Answer";
run;``````

Output

 Cluster 2016-11-28 2016-11-29 2016-11-30 2016-12-01 2016-12-02 2016-12-03 2016-12-04 Weekly Total MBL 27:16:00 28:03:00 24:58:00 29:14:00 32:51:00 . . 142 MBL DSC EMS 30:19:00 30:04:00 27:29:00 31:18:00 36:31:00 0:01 . 155 FAP 16:01 13:21 13:20 14:42 20:05 . . 77:29:00 OA 21:03 18:13 17:30 22:08 27:06:00 . . 106 PST 45:16:00 28:29:00 30:38:00 45:03:00 55:43:00 . . 205 YAS 32:00:00 31:22:00 25:58:00 35:25:00 42:25:00 . . 167 Oth Abs 16:01 15:19 15:20 18:57 29:31:00 . . 95:09:00 CMC 13:28 10:40 10:05 10:12 17:02 . . 61:26:00 CCP 2:57 2:10 1:14 2:24 1:29 . . 10:15 DHS 4:15 3:16 2:00 4:45 5:57 . . 20:14 Daily Totals 382 295 262 334 436 4:27 6:29 1721

Some of these calculations are out do to being exported out to excel.

Cheers

Super User
Posts: 6,935

## Re: Calculate field within Tabulate

PROC TABULATE doesn't do this.  PROC REPORT might, however.

PROC TABULATE permits just a single analysis variable per cell (although it allows many classification variables).  So the most you can get out of it are the usual statistics (mean, sum, etc.) plus percentage calculations.  But those percentage calculations have to be based on either N or SUM for a single analysis variable.

Super User
Posts: 13,942

## Re: Calculate field within Tabulate

Can you provide an example of the desired/expected output for the sample input data? I also suspect either a post to the forum error or something very odd you aren't explaining, Possibly related to ever letting Excel touch the data. Look at the location of DSC in the table, it appears where a sum should be and almost all of the values in the example output put appear to be times of some bizarre flavor.

You did nothing to have ASA appear in the output as a mean. By default the only statistic Tabulate will generate for Var variables is sum.

Possibly you intended: event_dt=" "*ASA=" *mean

but averaging precalculated rates generally isn't a good idea.

```data junk;
input miles gallons;
mpg= miles/gallons;
datalines;
1  1
100 5
;
run;

proc means data=junk mean;
var mpg;
run;

Which would show mean mpg of 10.5 but should be 1+100/1+5= 101/6 about 16.8```
Frequent Contributor
Posts: 129

## Re: Calculate field within Tabulate

Hi ballardw,

I def don't want to average an average - already have a hard time explaining to people about this.

The table got a bit screwy when I pasted it in - hence DSC appearing in a cell that should have had a numeric value.

The output is hours:minutes:seconds - excel changes the value but was using this as an example of output.

For the daily and weekly total I would nee to be able to use the

sum of TOTAL_ANSWER_TM / sum of CALLS_ANSWERED_CNT

The output I'm looking for is like this:

 Average Speed of Answer Cluster 2016-11-28 2016-11-29 2016-11-30 2016-12-01 2016-12-02 2016-12-03 2016-12-04 Weekly Total MBL DSC 27:16 28:03 24:58 29:14 32:51 28:18 EMS 30:19 30:04 27:29 31:18 36:31 00:01 30:58 FAP 16:01 13:21 13:20 14:42 20:05 15:16 OA 21:03 18:13 17:30 22:08 27:06 21:03 PST 45:16 28:29 30:38 45:03 55:43 39:47 YAS 32:00 31:22 25:58 35:25 42:25 32:21 Abs 16:01 15:19 15:20 18:57 29:31 18:45 CMC 13:28 10:40 10:05 10:12 17:02 12:17 CCP 02:57 02:10 01:14 02:24 01:29 01:59 Feedback 01:11 00:54 00:43 00:31 01:05 04:04 06:18 01:02 DHA 04:15 03:16 02:00 04:45 05:57 04:05 DCS 08:12 06:49 04:12 09:53 07:37 07:17 DA 03:43 03:28 06:01 05:20 15:27 06:40 Earn 11:16 08:38 05:46 09:54 14:59 09:29 EMG 00:04 01:56 00:02 00:22 00:04 00:41 External 18:49 13:52 16:38 13:40 12:45 15:08 FM 16:45 15:18 16:16 FPA 07:19 07:19 FIS 04:32 00:16 01:02 00:05 00:10 01:31 GI 00:09 00:40 00:02 00:23 IMC 15:00 09:21 07:06 10:38 20:02 00:11 00:04 08:46 Ind 29:31 15:28 15:12 17:02 24:24 20:28 MP 29:49 18:42 17:55 27:53 25:53 00:07 00:04 20:41 MVQ 00:12 00:20 01:00 00:16 00:45 00:30 OA 13:28 12:13 10:32 12:32 21:15 14:01 Oth 01:31 00:49 00:42 00:40 01:05 00:04 00:02 00:57 RC 00:21 00:26 00:22 00:39 00:40 00:29 Rur 10:22 05:54 05:50 08:29 16:18 09:13 SPTN 01:34 00:15 00:25 01:39 05:08 01:30 SW Daily Totals 17:07 14:28 13:06 15:53 20:35 00:25 00:22 15:32
Super User
Posts: 13,942

## Re: Calculate field within Tabulate

It may help to provide data in a form we can be sure is the same as you are using. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to generate data step code to recreate your example data that can be posted to the forum.

That would help with testing code.

Frequent Contributor
Posts: 129

## Re: Calculate field within Tabulate

Thanks Ballardw,

Unfortunately due to work restrictions I can't download the ZIP file to create the data.

Discussion stats
• 5 replies
• 356 views
• 0 likes
• 3 in conversation