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   ... View more

Hi All,   I'm wanting to know how to conditional format the top 5 results (from 28) for each day of the week in a proc Report. The final output is into PDF. Proc Report Data=work.Max_Abandon_New2_Trans style(report)=[BORDERCOLOR=BLACK BACKGROUND=#FFFFFF] STYLE(HEADER)={BACKGROUND=#008080 COLOR=WHITE FONTWEIGHT=BOLD BORDERCOLOR=BLACK} style(Column)={BORDERCOLOR=BLACK background=White COLOR=Black}; Columns MBL Cluster day_2 day_3 day_4 day_5 day_6 day_7 day_1; Define MBL / DISPLAY 'Business Line' Order; Define Cluster / DISPLAY 'Cluster'; Define day_2 / DISPLAY 'Monday' format=TIME8. ; Define day_3 / DISPLAY 'Tuesday' format=TIME8. ; Define day_4 / DISPLAY 'Wednesday' format=TIME8. ; Define day_5 / DISPLAY 'Thursday' format=TIME8. ; Define day_6 / DISPLAY 'Friday' format=TIME8. ; Define day_7 / DISPLAY 'Saturday' format=TIME8. ; Define day_1 / DISPLAY 'Sunday' format=TIME8. ; title3 color=Black font=Arial BOLD "Maximum Abandon Time (Week Ending &Excel_Date_Prompt)"; RUN; This is what I'm looking for in the output.     Any help is appreciated.   Cheers   I have added the following code that allows me to format anything over 1 Hour     compute day_2; if day_2 > 3600 then call define(_col_, 'style', 'style=[background= #E599A7 COLOR=#CC1B2B ]'); EndComp;   Can I make it do the 'top 5' though?   By using the following code I get the conditional formatting for the week however if it is a part Week ( Monday - Wednesday) way through the week it returns  ERROR: Variable day_5 is not on file WORK.MAX_ANSWER_NEW2_TRANS.   compute day_2; if day_2 > 3600 then call define(_col_, 'style', 'style=[background= #E599A7 COLOR=#CC1B2B ]'); EndComp; compute day_3; if day_3 > 3600 then call define(_col_, 'style', 'style=[background= #E599A7 COLOR=#CC1B2B ]'); EndComp; compute day_4; if day_4 > 3600 then call define(_col_, 'style', 'style=[background= #E599A7 COLOR=#CC1B2B ]'); EndComp; compute day_5; if day_5 > 3600 then call define(_col_, 'style', 'style=[background= #E599A7 COLOR=#CC1B2B ]'); EndComp; compute day_6; if day_6 > 3600 then call define(_col_, 'style', 'style=[background= #E599A7 COLOR=#CC1B2B ]'); EndComp; How can I get it to pass on the data (Days) that have not been populated yet? ... View more