Desktop productivity for business analysts and programmers

Cumulative totals to calculate Service Level

Reply
Frequent Contributor
Posts: 129

Cumulative totals to calculate Service Level

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_NMFY_MonthMonthQUEUE_TYPECLUSTER_IDCLUSTERSL_GroupEVENT_CNT
2016/20171July     SCCO1FAM300229016
2016/20171July     SCCO1FAM60062815
2016/20171July     SCCO1FAM90090529
2016/20171July     SCCO1FAM120062430
2016/20171July     SCCO1FAM150029361
2016/20171July     SCCO1FAM180012943
2016/20171July     SCCO1FAM21006070
2016/20171July     SCCO1FAM24002838
2016/20171July     SCCO1FAM27001869
2016/20171July     SCCO1FAM3000819
2016/20171July     SCCO1FAM3300662
2016/20171July     SCCO1FAM3600449
2016/20171July     SCCO1FAM36011015

 

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
3006009001200150018002100240027003000330036003601
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
PctSumPctSumPctSumPctSumPctSumPctSumPctSumPctSumPctSumPctSumPctSumPctSumPctSum
Financial YearClusterFY_Month423.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/2017FAM1
2565.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%
3416.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%
4307.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%
5330.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%
6189.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%
7150.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%
8166.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%
9183.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%
10117.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%
11328.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%
12311.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/2018FAM1109.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%
2101.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) :

  3006009001200150018002100240027003000330036003601
1July13.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%
2August21.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%
3September22.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%
4October13.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%
5November18.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%
6December15.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%
7January14.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%
8February15.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%
9March12.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%
10April9.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%
11May20.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%
12June16.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

 

Esteemed Advisor
Posts: 5,625

Re: Cumulative totals to calculate Service Level

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

PG
Trusted Advisor
Posts: 1,848

Re: Cumulative totals to calculate Service Level

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.);

 

 

Frequent Contributor
Posts: 129

Re: Cumulative totals to calculate Service Level

Thanks Shmuel,

 

That is a lot less coding - thanks for the help

 

Dean

Frequent Contributor
Posts: 129

Re: Cumulative totals to calculate Service Level

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
   3006009001200150018002100240027003000330036003601
   Event CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent CountEvent Count
   RowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSumRowPctSum
Financial YearClusterFY_Month45.7312.5418.0812.475.862.591.210.570.370.160.130.090.20
2016/2017FAM 1
251.949.8211.4710.226.694.112.611.380.770.460.260.110.17
350.477.819.438.777.597.204.492.191.160.390.200.170.13
439.405.568.9310.8212.0913.507.281.140.640.180.150.090.21
538.744.847.0010.1712.5615.487.742.150.470.260.250.150.20
631.875.464.673.614.748.3216.0215.316.701.850.580.260.62
723.653.413.764.566.9911.7717.5913.557.573.661.500.651.33
825.363.403.915.186.648.8115.7911.767.984.413.211.881.67
925.594.395.746.777.9812.7716.4911.624.152.350.940.560.65
1025.543.052.893.524.756.4513.0613.2314.055.812.251.124.28
1147.844.904.183.733.693.593.413.254.595.085.864.055.81
1246.264.214.514.745.295.205.435.605.675.373.542.181.99
2017/2018FAM 117.205.046.057.4910.6323.4323.124.391.380.680.300.140.15
221.695.884.363.284.058.4617.5315.2912.414.751.240.440.62

 

Regards

 

Dean

 

 

 

Super User
Posts: 24,010

Re: Cumulative totals to calculate Service Level

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.

Ask a Question
Discussion stats
  • 5 replies
  • 467 views
  • 2 likes
  • 4 in conversation