Cumulative totals to calculate Service Level

[DME790]

Hi All,

I need to calculate the Service Level percentage based at different seconds (SL_Group) percentage of the results based on the EVENT_CNT total for that cluster by month.

 A sample of data looks like thism (Extract from the the Proc Sum)

FINANCIAL_YEAR_NM	FY_Month	Month	QUEUE_TYPE	CLUSTER_ID	CLUSTER	SL_Group	EVENT_CNT
2016/2017	1	July	SCCO	1	FAM	300	229016
2016/2017	1	July	SCCO	1	FAM	600	62815
2016/2017	1	July	SCCO	1	FAM	900	90529
2016/2017	1	July	SCCO	1	FAM	1200	62430
2016/2017	1	July	SCCO	1	FAM	1500	29361
2016/2017	1	July	SCCO	1	FAM	1800	12943
2016/2017	1	July	SCCO	1	FAM	2100	6070
2016/2017	1	July	SCCO	1	FAM	2400	2838
2016/2017	1	July	SCCO	1	FAM	2700	1869
2016/2017	1	July	SCCO	1	FAM	3000	819
2016/2017	1	July	SCCO	1	FAM	3300	662
2016/2017	1	July	SCCO	1	FAM	3600	449
2016/2017	1	July	SCCO	1	FAM	3601	1015

My Full code looks like this:

ods excel file="/sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/misc/MBL Service Level.xlsx" ;

Title1 font=Arial BOLD "Service Level";


Proc Format;
	value FY_Month 	1='January'
		2='February'
		3='March'
		4='April'
		5='May'
		6='June'
		7='July'
		8='August'
		9='September'
		10='October'
		11='November'
		12='December'
		other='Invalid';
run;

Data Work.MBL_SL;
	Set CARTEL.preagent_telephony_gos;
	Keep  FINANCIAL_YEAR_NM month FY_month	QUEUE_TYPE cluster_id	CLUSTER	EVENT_TYPE	EVENT_CNt SL_Group;
	Where FINANCIAL_YEAR_NM in  ('2016/2017' '2017/2018')
		and Cluster_ID in (1 6 9 13 18 )
		and EVENT_TYPE = 'Answered';

	If EVENT_DURATION_VAL in (10 20 30 40 50 60 90 120 150 180 210 240 270 300) Then
		SL_Group = 300;
	else If EVENT_DURATION_VAL in (360 420 480 540 600 ) Then
		SL_Group = 600;
	else If EVENT_DURATION_VAL in (660 720 780 840 900  ) Then
		SL_Group = 900;
	else If EVENT_DURATION_VAL in (960 1020 1080 1140 1200) Then
		SL_Group = 1200;
	else If EVENT_DURATION_VAL in (1260 1320 1380 1440 1500 ) Then
		SL_Group = 1500;
	else If EVENT_DURATION_VAL in (1560 1620 1680 1740 1800 ) Then
		SL_Group = 1800;
	else If EVENT_DURATION_VAL in (1860 1920 1980 2040 2100 ) Then
		SL_Group = 2100;
	else If EVENT_DURATION_VAL in (2160 2220 2280 2340 2400 ) Then
		SL_Group = 2400;
	else If EVENT_DURATION_VAL in (2460 2520 2580 2640 2700 ) Then
		SL_Group = 2700;
	else If EVENT_DURATION_VAL in (2760 2820 2880 2940 3000 ) Then
		SL_Group = 3000;
	else If EVENT_DURATION_VAL in (3060 3120 3180 3240 3300 ) Then
		SL_Group =3300;
	else If EVENT_DURATION_VAL in (3360 3420 3480 3540 3600 ) Then
		SL_Group = 3600;
	ELSE SL_Group = 3601;
	Month = Month(EVENT_DT);

	If Month GE 1 and Month LE 6 then
		FY_Month = Month + 6;
	else FY_Month = month - 6;
	Format Month FY_Month.;
run;

Proc sort Data=work.mbl_sl;
	BY FINANCIAL_YEAR_NM fy_month Month QUEUE_TYPE cluster_id CLUSTEr EVENT_TYPE sl_group;
Run;

proc summary data=work.mbl_sl sum;
	by FINANCIAL_YEAR_NM FY_Month month queue_type cluster_id cluster sl_group;
	var event_cnt;
	output out=work.mbl_sl_sum (drop=_freq_ _type_) sum=;
run;


Title2 "Service Level FAM";

proc tabulate data=work.mbl_sl format= percent9.2;
	where cluster_id = 1;
	class FINANCIAL_YEAR_NM fy_month cluster sl_group;
	var event_cnt;

	table FINANCIAL_YEAR_NM * cluster * fy_month ,
		sl_group * event_cnt * ( pctsum);

		*/
		format FY_Month FY_Month.;
run;

proc tabulate data=work.mbl_sl;
	where cluster_id = 6;
	class FINANCIAL_YEAR_NM fy_month cluster sl_group;
	var event_cnt;

	table financial_year_nm * cluster * fy_month ,
		sl_group * event_cnt * (pctsum);

		*/
		format FY_Month FY_Month.;
run;

title1;
title2;
ODS EXCEL CLOSE;

and the output looks like this (it isn't calculating the percentage right either);

	SL_Group
300	600	900	1200	1500	1800	2100	2400	2700	3000	3300	3600	3601
Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count
PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum	PctSum
Financial Year	Cluster	FY_Month	423.95%	116.28%	167.58%	115.58%	54.36%	23.97%	11.24%	5.25%	3.46%	1.52%	1.23%	0.83%	1.88%
2016/2017	FAM	1
2	565.68%	106.90%	124.91%	111.28%	72.88%	44.81%	28.40%	14.98%	8.44%	5.06%	2.78%	1.19%	1.85%
3	416.96%	64.55%	77.90%	72.49%	62.71%	59.51%	37.07%	18.06%	9.59%	3.25%	1.65%	1.45%	1.03%
4	307.25%	43.36%	69.66%	84.40%	94.30%	105.30%	56.76%	8.85%	5.01%	1.43%	1.14%	0.70%	1.62%
5	330.52%	41.27%	59.72%	86.74%	107.14%	132.04%	66.02%	18.30%	4.01%	2.24%	2.15%	1.26%	1.69%
6	189.20%	32.41%	27.75%	21.46%	28.14%	49.37%	95.10%	90.89%	39.76%	10.96%	3.44%	1.54%	3.69%
7	150.85%	21.73%	23.95%	29.10%	44.59%	75.08%	112.18%	86.41%	48.25%	23.35%	9.57%	4.17%	8.49%
8	166.73%	22.33%	25.72%	34.06%	43.65%	57.93%	103.83%	77.36%	52.48%	29.03%	21.10%	12.39%	10.96%
9	183.41%	31.47%	41.14%	48.50%	57.17%	91.54%	118.22%	83.26%	29.76%	16.85%	6.76%	4.00%	4.68%
10	117.02%	13.98%	13.22%	16.11%	21.77%	29.53%	59.82%	60.63%	64.39%	26.60%	10.32%	5.14%	19.62%
11	328.68%	33.65%	28.75%	25.66%	25.38%	24.69%	23.42%	22.34%	31.53%	34.91%	40.26%	27.84%	39.93%
12	311.15%	28.31%	30.33%	31.88%	35.56%	34.96%	36.55%	37.65%	38.16%	36.12%	23.84%	14.67%	13.39%
2017/2018	FAM	1	109.06%	31.95%	38.34%	47.51%	67.41%	148.51%	146.59%	27.81%	8.73%	4.28%	1.90%	0.91%	0.93%
2	101.29%	27.45%	20.39%	15.31%	18.94%	39.53%	81.89%	71.42%	57.96%	22.20%	5.77%	2.08%	2.88%

I would like the output to look like this (done in excel) in financial year order (Jul - Jun) :

		300	600	900	1200	1500	1800	2100	2400	2700	3000	3300	3600	3601
1	July	13.63%	22.25%	35.22%	46.89%	56.25%	64.41%	71.00%	74.97%	77.06%	78.87%	80.27%	81.75%	100.00%
2	August	21.81%	31.14%	41.65%	52.58%	61.69%	69.23%	75.18%	78.30%	80.16%	81.68%	83.30%	84.86%	100.00%
3	September	22.81%	30.80%	39.65%	48.01%	56.77%	66.64%	74.53%	80.93%	85.06%	87.91%	89.72%	91.31%	100.00%
4	October	13.46%	16.94%	23.48%	32.55%	43.91%	59.87%	72.98%	79.21%	83.09%	85.76%	88.06%	90.14%	100.00%
5	November	18.32%	22.49%	28.16%	35.68%	45.68%	60.53%	72.11%	78.78%	82.71%	85.00%	86.96%	88.87%	100.00%
6	December	15.44%	18.77%	22.88%	27.13%	31.78%	38.78%	50.52%	66.17%	79.22%	84.09%	86.34%	88.14%	100.00%
7	January	14.10%	17.08%	20.36%	23.77%	27.96%	36.78%	52.35%	69.55%	82.65%	89.28%	92.45%	94.00%	100.00%
8	February	15.59%	19.32%	22.90%	26.87%	31.02%	37.37%	49.93%	65.53%	78.31%	86.77%	91.38%	94.02%	100.00%
9	March	12.97%	16.60%	20.60%	25.58%	31.45%	41.35%	57.18%	72.23%	82.41%	87.65%	90.95%	93.14%	100.00%
10	April	9.80%	11.76%	14.09%	16.91%	20.25%	25.06%	33.97%	47.04%	62.88%	76.83%	83.60%	87.06%	100.00%
11	May	20.72%	24.33%	27.73%	30.89%	33.97%	36.77%	39.73%	43.22%	47.35%	52.59%	60.26%	69.61%	100.00%
12	June	16.73%	20.19%	23.88%	27.39%	31.48%	35.93%	41.69%	48.08%	54.22%	60.61%	67.98%	76.78%	100.00%

Any help is appreciated.

Cheers

Dean

[PGStats]

I think you need ROWPCTSUM instead of PCTSUM in your table statements.

[Shmuel]

May I suggest replacing the IF ... THEN ... ELSE ... staement into using format.

It will shorten your code and enable maintanance through format without changing the code itself:

proc format lib=work;
  value slgroup
    10 -  300 = '300
   360 -  600 = '600'
   660 -  900 = '900'
   960 - 1200 = '1200'
  1260 - 1500 = '1500'
  1560 - 1800 = '1800'
  ... etc ...
  3600 - high = '3601'
  ; run;

  /**** OR - in case of mid values not mentioned in code ****/
proc format lib=work;
  value slgroup 
     10 = '300'
     20 = '300'
     30 = '300'
	 ... 
   3360 = '3600'
   3420 = '3600'
   3480 = '3600'
   3540 = '3600'
   3600 = '3600'
   other = '3601'
  ; run;
  

/*** then replace the if ... then... else... into ***/

    SL_GROUP = input(put(event_duration_val,slgroup.),best4.);

[DME790]

Thanks Shmuel,

That is a lot less coding - thanks for the help

Dean

[DME790]

Hi PG,

Thanks for the response.

The ROWPCTSUM gives me the individual percentage for each SL_GROUP. Can I format this to show the percentage symbol (percent9.0).

I would like to have the output show:

300 = 45.73

600 = 45.73 + 12.54 = 58.27

900 = 45.73 + 12.54 + 18.08 = 76.34

etc etc.

Is there a way of doing this in the proc tabulate or should I be using another proc?

			SL_Group
			300	600	900	1200	1500	1800	2100	2400	2700	3000	3300	3600	3601
			Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count	Event Count
			RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum	RowPctSum
Financial Year	Cluster	FY_Month	45.73	12.54	18.08	12.47	5.86	2.59	1.21	0.57	0.37	0.16	0.13	0.09	0.20
2016/2017	FAM	1
2	51.94	9.82	11.47	10.22	6.69	4.11	2.61	1.38	0.77	0.46	0.26	0.11	0.17
3	50.47	7.81	9.43	8.77	7.59	7.20	4.49	2.19	1.16	0.39	0.20	0.17	0.13
4	39.40	5.56	8.93	10.82	12.09	13.50	7.28	1.14	0.64	0.18	0.15	0.09	0.21
5	38.74	4.84	7.00	10.17	12.56	15.48	7.74	2.15	0.47	0.26	0.25	0.15	0.20
6	31.87	5.46	4.67	3.61	4.74	8.32	16.02	15.31	6.70	1.85	0.58	0.26	0.62
7	23.65	3.41	3.76	4.56	6.99	11.77	17.59	13.55	7.57	3.66	1.50	0.65	1.33
8	25.36	3.40	3.91	5.18	6.64	8.81	15.79	11.76	7.98	4.41	3.21	1.88	1.67
9	25.59	4.39	5.74	6.77	7.98	12.77	16.49	11.62	4.15	2.35	0.94	0.56	0.65
10	25.54	3.05	2.89	3.52	4.75	6.45	13.06	13.23	14.05	5.81	2.25	1.12	4.28
11	47.84	4.90	4.18	3.73	3.69	3.59	3.41	3.25	4.59	5.08	5.86	4.05	5.81
12	46.26	4.21	4.51	4.74	5.29	5.20	5.43	5.60	5.67	5.37	3.54	2.18	1.99
2017/2018	FAM	1	17.20	5.04	6.05	7.49	10.63	23.43	23.12	4.39	1.38	0.68	0.30	0.14	0.15
2	21.69	5.88	4.36	3.28	4.05	8.46	17.53	15.29	12.41	4.75	1.24	0.44	0.62

Regards

Dean

[Reeza]

PROC REPORT may be able to do a running total, but it would probably be easiest to do it manually (via a data step) and then use PROC PRINT/REPORT to display your results.