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
I think you need ROWPCTSUM instead of PCTSUM in your table statements.
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.);
Thanks Shmuel,
That is a lot less coding - thanks for the help
Dean
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
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.
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.
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.