topic Calculate field within Tabulate in SAS Enterprise Guide

Calculate field within Tabulate

DME790 — Mon, 05 Dec 2016 01:46:52 GMT

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.

Total_Answer_TM / Calls_Answered_CNT = ASA.

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;
	VAR CALLS_ANSWERED_CNT TOTAL_ANSWER_TM;
	Output out=Report.SSW_ASA_SUM (DROP=_TYPE_ _FREQ_) SUM=;
Run;

Data Work.SSW_ASA_2;
	Set Work.SSW_ASA_SUM;
	ASA = (TOTAL_ANSWER_TM/CALLS_ANSWERED_CNT);
	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

Re: Calculate field within Tabulate

Astounding — Mon, 05 Dec 2016 07:12:13 GMT

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.

Re: Calculate field within Tabulate

ballardw — Mon, 05 Dec 2016 15:22:02 GMT

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

Re: Calculate field within Tabulate

DME790 — Tue, 06 Dec 2016 02:48:00 GMT

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

Re: Calculate field within Tabulate

ballardw — Tue, 06 Dec 2016 16:30:19 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Calculate field within Tabulate

DME790 — Wed, 07 Dec 2016 04:13:01 GMT

Thanks Ballardw,

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