BookmarkSubscribeRSS Feed
DME790
Pyrite | Level 9

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

 

5 REPLIES 5
PGStats
Opal | Level 21

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

PG
Shmuel
Garnet | Level 18

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
Pyrite | Level 9

Thanks Shmuel,

 

That is a lot less coding - thanks for the help

 

Dean

DME790
Pyrite | Level 9

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

 

 

 

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1147 views
  • 2 likes
  • 4 in conversation