Desktop productivity for business analysts and programmers

Calculate field within Tabulate

Reply
Contributor
Posts: 48

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.

 

Total_Answer_TM / Calls_Answered_CNT = ASA.

 

Sample of Data

WEEK_END_DTEVENT_DTMBLCLUSTERCALLS_ANSWERED_CNTTOTAL_ANSWER_TMASA
4/12/201628/11/2016MBLDSC75301232147527:16
4/12/201628/11/2016MBLEMS79691449694630:19
4/12/201628/11/2016MBLDFAP217692091068816:01
4/12/201628/11/2016MBLOA5217658813821:03
4/12/201628/11/2016MBLPST3360912517245:16
4/12/201628/11/2016MBLYAS4724906954832:00
4/12/201628/11/2016OthABS61859401316:01
4/12/201628/11/2016OthCMC3719300407013:28
4/12/201628/11/2016OthCCP18318302: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

 

Cluster2016-11-282016-11-292016-11-302016-12-012016-12-022016-12-032016-12-04Weekly Total
MBL 27:16:0028:03:0024:58:0029:14:0032:51:00..142
MBLDSC
EMS30:19:0030:04:0027:29:0031:18:0036:31:000:01.155
FAP16:0113:2113:2014:4220:05..77:29:00
OA21:0318:1317:3022:0827:06:00..106
PST45:16:0028:29:0030:38:0045:03:0055:43:00..205
YAS32:00:0031:22:0025:58:0035:25:0042:25:00..167
OthAbs16:0115:1915:2018:5729:31:00..95:09:00
CMC13:2810:4010:0510:1217:02..61:26:00
CCP2:572:101:142:241:29..10:15
DHS4:153:162:004:455:57..20:14
Daily Totals3822952623344364:276:291721

 

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

 

Cheers

 

 

 

Respected Advisor
Posts: 4,995

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.

Grand Advisor
Posts: 10,223

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
Contributor
Posts: 48

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
          
Cluster2016-11-282016-11-292016-11-302016-12-012016-12-022016-12-032016-12-04Weekly Total
MBL         
DSC27:1628:0324:5829:1432:51   28:18 
EMS30:1930:0427:2931:1836:3100:01 30:58
FAP16:0113:2113:2014:4220:05  15:16
OA21:0318:1317:3022:0827:06  21:03
PST45:1628:2930:3845:0355:43  39:47
YAS32:0031:2225:5835:2542:25  32:21
Abs16:0115:1915:2018:5729:31  18:45 
CMC13:2810:4010:0510:1217:02  12:17
CCP02:5702:1001:1402:2401:29  01:59
Feedback01:1100:5400:4300:3101:0504:0406:1801:02
DHA04:1503:1602:0004:4505:57  04:05
DCS08:1206:4904:1209:5307:37  07:17
DA03:4303:2806:0105:2015:27  06:40
Earn11:1608:3805:4609:5414:59  09:29
EMG00:0401:5600:0200:2200:04  00:41
External18:4913:5216:3813:4012:45  15:08
FM16:4515:18     16:16
FPA07:19      07:19
FIS04:3200:1601:0200:0500:10  01:31
GI  00:0900:4000:02  00:23
IMC15:0009:2107:0610:3820:0200:1100:0408:46
Ind29:3115:2815:1217:0224:24  20:28
MP29:4918:4217:5527:5325:5300:0700:0420:41
MVQ00:1200:2001:0000:1600:45  00:30
OA13:2812:1310:3212:3221:15  14:01
Oth01:3100:4900:4200:4001:0500:0400:0200:57
RC00:2100:2600:2200:3900:40  00:29
Rur10:2205:5405:5008:2916:18  09:13
SPTN01:3400:1500:2501:3905:08  01:30
SW        
Daily Totals17:0714:2813:0615:5320:3500:2500:22

15:32

Grand Advisor
Posts: 10,223

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.

Contributor
Posts: 48

Re: Calculate field within Tabulate

Thanks Ballardw,

 

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

 

 

Ask a Question
Discussion stats
  • 5 replies
  • 244 views
  • 0 likes
  • 3 in conversation