366.CUSTOMER SUPPORT CENTERS PERFORMANCE AND EFFICIENCY ANALYSIS USING PROC SQL | PROC MEANS | PROC REG | PROC SGPLOT | DATA STEP | MACROS | DATE FUNCTIONS (MDY-INTNX-INTCK) | MERGE | APPEND | SET | TRANSPOSE

CUSTOMER SUPPORT CENTERS PERFORMANCE AND EFFICIENCY ANALYSIS USING PROC SQL |  PROC MEANS | PROC REG | PROC SGPLOT | DATA STEP | MACROS | DATE FUNCTIONS  (MDY-INTNX-INTCK) | MERGE | APPEND | SET | TRANSPOSE

 options nocenter;

1.Creating the Raw Customer Support Dataset

data support_raw; 

    length Company_Name $20;

    format Report_Date date9.;

    infile datalines dlm='|' dsd;

    input Company_Name $ Tickets_Per_Day Resolution_Time Satisfaction_Score 

          Staff_Count Report_Date : date9.;

    datalines;

QuickAssist|380|5.1|85|110|05JAN2024

CallEase|460|7.5|78|135|08JAN2024

SupportPro|500|6.0|88|150|12JAN2024

HelpDeskNow|340|4.8|90|95|15JAN2024

VoiceCare|390|6.8|80|115|18JAN2024

AnswerHub|300|4.5|92|90|22JAN2024

SolveFast|450|5.4|87|130|25JAN2024

ServicePlus|410|5.9|84|125|28JAN2024

CareConnect|470|7.2|79|140|30JAN2024

TechResolve|480|6.6|81|145|02FEB2024

CustomerFirst|360|4.9|89|100|05FEB2024

;

run;

proc print data=support_raw;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count
1	QuickAssist	05JAN2024	380	5.1	85	110
2	CallEase	08JAN2024	460	7.5	78	135
3	SupportPro	12JAN2024	500	6.0	88	150
4	HelpDeskNow	15JAN2024	340	4.8	90	95
5	VoiceCare	18JAN2024	390	6.8	80	115
6	AnswerHub	22JAN2024	300	4.5	92	90
7	SolveFast	25JAN2024	450	5.4	87	130
8	ServicePlus	28JAN2024	410	5.9	84	125
9	CareConnect	30JAN2024	470	7.2	79	140
10	TechResolve	02FEB2024	480	6.6	81	145
11	CustomerFirst	05FEB2024	360	4.9	89	100

2.Creating Future and Past Report Dates Using INTNX

data support_dates;

    set support_raw;

    Next_Report = intnx('month', Report_Date, 1);

    Prev_Report = intnx('month', Report_Date, -1);

    format Next_Report Prev_Report date9.;

run;

proc print data=support_dates;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count	Next_Report	Prev_Report
1	QuickAssist	05JAN2024	380	5.1	85	110	01FEB2024	01DEC2023
2	CallEase	08JAN2024	460	7.5	78	135	01FEB2024	01DEC2023
3	SupportPro	12JAN2024	500	6.0	88	150	01FEB2024	01DEC2023
4	HelpDeskNow	15JAN2024	340	4.8	90	95	01FEB2024	01DEC2023
5	VoiceCare	18JAN2024	390	6.8	80	115	01FEB2024	01DEC2023
6	AnswerHub	22JAN2024	300	4.5	92	90	01FEB2024	01DEC2023
7	SolveFast	25JAN2024	450	5.4	87	130	01FEB2024	01DEC2023
8	ServicePlus	28JAN2024	410	5.9	84	125	01FEB2024	01DEC2023
9	CareConnect	30JAN2024	470	7.2	79	140	01FEB2024	01DEC2023
10	TechResolve	02FEB2024	480	6.6	81	145	01MAR2024	01JAN2024
11	CustomerFirst	05FEB2024	360	4.9	89	100	01MAR2024	01JAN2024

3.Calculate Days Between Reports Using INTCK

data support_gap;

    set support_dates;

    Days_Between = intck('day', Prev_Report, Next_Report);

run;

proc print data=support_gap;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count	Next_Report	Prev_Report	Days_Between
1	QuickAssist	05JAN2024	380	5.1	85	110	01FEB2024	01DEC2023	62
2	CallEase	08JAN2024	460	7.5	78	135	01FEB2024	01DEC2023	62
3	SupportPro	12JAN2024	500	6.0	88	150	01FEB2024	01DEC2023	62
4	HelpDeskNow	15JAN2024	340	4.8	90	95	01FEB2024	01DEC2023	62
5	VoiceCare	18JAN2024	390	6.8	80	115	01FEB2024	01DEC2023	62
6	AnswerHub	22JAN2024	300	4.5	92	90	01FEB2024	01DEC2023	62
7	SolveFast	25JAN2024	450	5.4	87	130	01FEB2024	01DEC2023	62
8	ServicePlus	28JAN2024	410	5.9	84	125	01FEB2024	01DEC2023	62
9	CareConnect	30JAN2024	470	7.2	79	140	01FEB2024	01DEC2023	62
10	TechResolve	02FEB2024	480	6.6	81	145	01MAR2024	01JAN2024	60
11	CustomerFirst	05FEB2024	360	4.9	89	100	01MAR2024	01JAN2024	60

4.Performance Index Creation Using DATA Step

data support_perf;

    set support_gap;

    Performance_Index = (Satisfaction_Score * 100) / (Resolution_Time * Tickets_Per_Day);

run;

proc print data=support_perf;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count	Next_Report	Prev_Report	Days_Between	Performance_Index
1	QuickAssist	05JAN2024	380	5.1	85	110	01FEB2024	01DEC2023	62	4.38596
2	CallEase	08JAN2024	460	7.5	78	135	01FEB2024	01DEC2023	62	2.26087
3	SupportPro	12JAN2024	500	6.0	88	150	01FEB2024	01DEC2023	62	2.93333
4	HelpDeskNow	15JAN2024	340	4.8	90	95	01FEB2024	01DEC2023	62	5.51471
5	VoiceCare	18JAN2024	390	6.8	80	115	01FEB2024	01DEC2023	62	3.01659
6	AnswerHub	22JAN2024	300	4.5	92	90	01FEB2024	01DEC2023	62	6.81481
7	SolveFast	25JAN2024	450	5.4	87	130	01FEB2024	01DEC2023	62	3.58025
8	ServicePlus	28JAN2024	410	5.9	84	125	01FEB2024	01DEC2023	62	3.47251
9	CareConnect	30JAN2024	470	7.2	79	140	01FEB2024	01DEC2023	62	2.33452
10	TechResolve	02FEB2024	480	6.6	81	145	01MAR2024	01JAN2024	60	2.55682
11	CustomerFirst	05FEB2024	360	4.9	89	100	01MAR2024	01JAN2024	60	5.04535

5.SQL Summary of Customer Support Centers

proc sql;

    create table support_summary as

    select Company_Name,

           avg(Tickets_Per_Day) as Avg_Tickets,

           avg(Resolution_Time) as Avg_Resolution,

           avg(Satisfaction_Score) as Avg_Satisfaction,

           sum(Staff_Count) as Total_Staff

    from support_perf

    group by Company_Name;

quit;

proc print data=support_summary;

run;

OUTPUT:

Obs	Company_Name	Avg_Tickets	Avg_Resolution	Avg_Satisfaction	Total_Staff
1	AnswerHub	300	4.5	92	90
2	CallEase	460	7.5	78	135
3	CareConnect	470	7.2	79	140
4	CustomerFirst	360	4.9	89	100
5	HelpDeskNow	340	4.8	90	95
6	QuickAssist	380	5.1	85	110
7	ServicePlus	410	5.9	84	125
8	SolveFast	450	5.4	87	130
9	SupportPro	500	6.0	88	150
10	TechResolve	480	6.6	81	145
11	VoiceCare	390	6.8	80	115

6.Statistical Analysis – PROC MEANS

proc means data=support_perf mean min max;

    var Tickets_Per_Day Resolution_Time Satisfaction_Score Staff_Count Performance_Index;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum
Tickets_Per_Day Resolution_Time Satisfaction_Score Staff_Count Performance_Index	412.7272727 5.8818182 84.8181818 121.3636364 3.8105201	300.0000000 4.5000000 78.0000000 90.0000000 2.2608696	500.0000000 7.5000000 92.0000000 150.0000000 6.8148148

7.Regression Analysis – Impact of Staff on Satisfaction

proc reg data=support_perf;

    model Satisfaction_Score = Staff_Count Tickets_Per_Day Resolution_Time;

run;

quit;

OUTPUT:

The REG Procedure

Model: MODEL1

Dependent Variable: Satisfaction_Score

Number of Observations Read	11
Number of Observations Used	11

Analysis of Variance
Source	DF	Sum of Squares	Mean Square	F Value	Pr > F
Model	3	206.14919	68.71640	20.48	0.0008
Error	7	23.48718	3.35531
Corrected Total	10	229.63636

Root MSE	1.83175	R-Square	0.8977
Dependent Mean	84.81818	Adj R-Sq	0.8539
Coeff Var	2.15962

Parameter Estimates
Variable	DF	Parameter Estimate	Standard Error	t Value	Pr > |t|
Intercept	1	109.45317	4.43841	24.66	<.0001
Staff_Count	1	0.07780	0.19558	0.40	0.7026
Tickets_Per_Day	1	-0.01078	0.06170	-0.17	0.8662
Resolution_Time	1	-5.03708	0.84092	-5.99	0.0005

The REG Procedure

Model: MODEL1

Dependent Variable: Satisfaction_Score

8.Visualization – PROC SGPLOT

proc sgplot data=support_perf;

    scatter x=Tickets_Per_Day y=Satisfaction_Score;

    reg x=Tickets_Per_Day y=Satisfaction_Score;

run;

OUTPUT:

9.Macro for Performance Ranking

%macro rank_performance;

    data support_rank;

       length Rank $20.;

        set support_perf;

        if Performance_Index > 4 then Rank="EXCELLENT";

        else if Performance_Index > 3 then Rank="GOOD";

        else Rank="NEEDS IMPROVEMENT";

    run;

proc print data=support_rank;

    run;

%mend;

%rank_performance;

OUTPUT:

Obs	Rank	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count	Next_Report	Prev_Report	Days_Between	Performance_Index
1	EXCELLENT	QuickAssist	05JAN2024	380	5.1	85	110	01FEB2024	01DEC2023	62	4.38596
2	NEEDS IMPROVEMENT	CallEase	08JAN2024	460	7.5	78	135	01FEB2024	01DEC2023	62	2.26087
3	NEEDS IMPROVEMENT	SupportPro	12JAN2024	500	6.0	88	150	01FEB2024	01DEC2023	62	2.93333
4	EXCELLENT	HelpDeskNow	15JAN2024	340	4.8	90	95	01FEB2024	01DEC2023	62	5.51471
5	GOOD	VoiceCare	18JAN2024	390	6.8	80	115	01FEB2024	01DEC2023	62	3.01659
6	EXCELLENT	AnswerHub	22JAN2024	300	4.5	92	90	01FEB2024	01DEC2023	62	6.81481
7	GOOD	SolveFast	25JAN2024	450	5.4	87	130	01FEB2024	01DEC2023	62	3.58025
8	GOOD	ServicePlus	28JAN2024	410	5.9	84	125	01FEB2024	01DEC2023	62	3.47251
9	NEEDS IMPROVEMENT	CareConnect	30JAN2024	470	7.2	79	140	01FEB2024	01DEC2023	62	2.33452
10	NEEDS IMPROVEMENT	TechResolve	02FEB2024	480	6.6	81	145	01MAR2024	01JAN2024	60	2.55682
11	EXCELLENT	CustomerFirst	05FEB2024	360	4.9	89	100	01MAR2024	01JAN2024	60	5.04535

10.Transpose for Reporting

proc transpose data=support_rank out=support_transposed;

    by Company_Name NotSorted;

    var Tickets_Per_Day Resolution_Time Satisfaction_Score;

run;

proc print data=support_transposed;

run;

OUTPUT:

Obs	Company_Name	_NAME_	COL1
1	QuickAssist	Tickets_Per_Day	380.0
2	QuickAssist	Resolution_Time	5.1
3	QuickAssist	Satisfaction_Score	85.0
4	CallEase	Tickets_Per_Day	460.0
5	CallEase	Resolution_Time	7.5
6	CallEase	Satisfaction_Score	78.0
7	SupportPro	Tickets_Per_Day	500.0
8	SupportPro	Resolution_Time	6.0
9	SupportPro	Satisfaction_Score	88.0
10	HelpDeskNow	Tickets_Per_Day	340.0
11	HelpDeskNow	Resolution_Time	4.8
12	HelpDeskNow	Satisfaction_Score	90.0
13	VoiceCare	Tickets_Per_Day	390.0
14	VoiceCare	Resolution_Time	6.8
15	VoiceCare	Satisfaction_Score	80.0
16	AnswerHub	Tickets_Per_Day	300.0
17	AnswerHub	Resolution_Time	4.5
18	AnswerHub	Satisfaction_Score	92.0
19	SolveFast	Tickets_Per_Day	450.0
20	SolveFast	Resolution_Time	5.4
21	SolveFast	Satisfaction_Score	87.0
22	ServicePlus	Tickets_Per_Day	410.0
23	ServicePlus	Resolution_Time	5.9
24	ServicePlus	Satisfaction_Score	84.0
25	CareConnect	Tickets_Per_Day	470.0
26	CareConnect	Resolution_Time	7.2
27	CareConnect	Satisfaction_Score	79.0
28	TechResolve	Tickets_Per_Day	480.0
29	TechResolve	Resolution_Time	6.6
30	TechResolve	Satisfaction_Score	81.0
31	CustomerFirst	Tickets_Per_Day	360.0
32	CustomerFirst	Resolution_Time	4.9
33	CustomerFirst	Satisfaction_Score	89.0

11.Creating a New Month Dataset

data support_feb; 

    length Company_Name $20;

    format Report_Date date9.;

    infile datalines dlm='|' dsd;

    input Company_Name $ Tickets_Per_Day Resolution_Time Satisfaction_Score 

          Staff_Count Report_Date : date9.;

    datalines;

TeleHelp|440|6.0|84|122|01FEB2024

QuickAssist|395|5.0|86|112|01FEB2024

;

run;

proc print data=support_feb;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count
1	TeleHelp	01FEB2024	440	6	84	122
2	QuickAssist	01FEB2024	395	5	86	112

12.Append February Data

proc append base=support_raw 

            data=support_feb;

run;

proc print data=support_raw;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count
1	QuickAssist	05JAN2024	380	5.1	85	110
2	CallEase	08JAN2024	460	7.5	78	135
3	SupportPro	12JAN2024	500	6.0	88	150
4	HelpDeskNow	15JAN2024	340	4.8	90	95
5	VoiceCare	18JAN2024	390	6.8	80	115
6	AnswerHub	22JAN2024	300	4.5	92	90
7	SolveFast	25JAN2024	450	5.4	87	130
8	ServicePlus	28JAN2024	410	5.9	84	125
9	CareConnect	30JAN2024	470	7.2	79	140
10	TechResolve	02FEB2024	480	6.6	81	145
11	CustomerFirst	05FEB2024	360	4.9	89	100
12	TeleHelp	01FEB2024	440	6.0	84	122
13	QuickAssist	01FEB2024	395	5.0	86	112

13.Merge Example

proc sort data=support_raw; by Company_Name; run;

proc print data=support_raw;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count
1	AnswerHub	22JAN2024	300	4.5	92	90
2	CallEase	08JAN2024	460	7.5	78	135
3	CareConnect	30JAN2024	470	7.2	79	140
4	CustomerFirst	05FEB2024	360	4.9	89	100
5	HelpDeskNow	15JAN2024	340	4.8	90	95
6	QuickAssist	05JAN2024	380	5.1	85	110
7	QuickAssist	01FEB2024	395	5.0	86	112
8	ServicePlus	28JAN2024	410	5.9	84	125
9	SolveFast	25JAN2024	450	5.4	87	130
10	SupportPro	12JAN2024	500	6.0	88	150
11	TechResolve	02FEB2024	480	6.6	81	145
12	TeleHelp	01FEB2024	440	6.0	84	122
13	VoiceCare	18JAN2024	390	6.8	80	115

proc sort data=support_rank; by Company_Name; run;

proc print data=support_rank;

run;

OUTPUT:

Obs	Rank	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count	Next_Report	Prev_Report	Days_Between	Performance_Index
1	EXCELLENT	AnswerHub	22JAN2024	300	4.5	92	90	01FEB2024	01DEC2023	62	6.81481
2	NEEDS IMPROVEMENT	CallEase	08JAN2024	460	7.5	78	135	01FEB2024	01DEC2023	62	2.26087
3	NEEDS IMPROVEMENT	CareConnect	30JAN2024	470	7.2	79	140	01FEB2024	01DEC2023	62	2.33452
4	EXCELLENT	CustomerFirst	05FEB2024	360	4.9	89	100	01MAR2024	01JAN2024	60	5.04535
5	EXCELLENT	HelpDeskNow	15JAN2024	340	4.8	90	95	01FEB2024	01DEC2023	62	5.51471
6	EXCELLENT	QuickAssist	05JAN2024	380	5.1	85	110	01FEB2024	01DEC2023	62	4.38596
7	GOOD	ServicePlus	28JAN2024	410	5.9	84	125	01FEB2024	01DEC2023	62	3.47251
8	GOOD	SolveFast	25JAN2024	450	5.4	87	130	01FEB2024	01DEC2023	62	3.58025
9	NEEDS IMPROVEMENT	SupportPro	12JAN2024	500	6.0	88	150	01FEB2024	01DEC2023	62	2.93333
10	NEEDS IMPROVEMENT	TechResolve	02FEB2024	480	6.6	81	145	01MAR2024	01JAN2024	60	2.55682
11	GOOD	VoiceCare	18JAN2024	390	6.8	80	115	01FEB2024	01DEC2023	62	3.01659

data support_merge;

    merge support_raw support_rank;

    by Company_Name;

run;

proc print data=support_merge;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count	Rank	Next_Report	Prev_Report	Days_Between	Performance_Index
1	AnswerHub	22JAN2024	300	4.5	92	90	EXCELLENT	01FEB2024	01DEC2023	62	6.81481
2	CallEase	08JAN2024	460	7.5	78	135	NEEDS IMPROVEMENT	01FEB2024	01DEC2023	62	2.26087
3	CareConnect	30JAN2024	470	7.2	79	140	NEEDS IMPROVEMENT	01FEB2024	01DEC2023	62	2.33452
4	CustomerFirst	05FEB2024	360	4.9	89	100	EXCELLENT	01MAR2024	01JAN2024	60	5.04535
5	HelpDeskNow	15JAN2024	340	4.8	90	95	EXCELLENT	01FEB2024	01DEC2023	62	5.51471
6	QuickAssist	05JAN2024	380	5.1	85	110	EXCELLENT	01FEB2024	01DEC2023	62	4.38596
7	QuickAssist	01FEB2024	395	5.0	86	112	EXCELLENT	01FEB2024	01DEC2023	62	4.38596
8	ServicePlus	28JAN2024	410	5.9	84	125	GOOD	01FEB2024	01DEC2023	62	3.47251
9	SolveFast	25JAN2024	450	5.4	87	130	GOOD	01FEB2024	01DEC2023	62	3.58025
10	SupportPro	12JAN2024	500	6.0	88	150	NEEDS IMPROVEMENT	01FEB2024	01DEC2023	62	2.93333
11	TechResolve	02FEB2024	480	6.6	81	145	NEEDS IMPROVEMENT	01MAR2024	01JAN2024	60	2.55682
12	TeleHelp	01FEB2024	440	6.0	84	122		.	.	.	.
13	VoiceCare	18JAN2024	390	6.8	80	115	GOOD	01FEB2024	01DEC2023	62	3.01659

14.Set Statement Example

data support_all;

    set support_raw 

        support_feb;

run;

proc print data=support_all;

run;

OUTPUT:

Obs	Company_Name	Report_Date	Tickets_Per_Day	Resolution_Time	Satisfaction_Score	Staff_Count
1	AnswerHub	22JAN2024	300	4.5	92	90
2	CallEase	08JAN2024	460	7.5	78	135
3	CareConnect	30JAN2024	470	7.2	79	140
4	CustomerFirst	05FEB2024	360	4.9	89	100
5	HelpDeskNow	15JAN2024	340	4.8	90	95
6	QuickAssist	05JAN2024	380	5.1	85	110
7	QuickAssist	01FEB2024	395	5.0	86	112
8	ServicePlus	28JAN2024	410	5.9	84	125
9	SolveFast	25JAN2024	450	5.4	87	130
10	SupportPro	12JAN2024	500	6.0	88	150
11	TechResolve	02FEB2024	480	6.6	81	145
12	TeleHelp	01FEB2024	440	6.0	84	122
13	VoiceCare	18JAN2024	390	6.8	80	115
14	TeleHelp	01FEB2024	440	6.0	84	122
15	QuickAssist	01FEB2024	395	5.0	86	112

To Visit My Previous Different Types Of Oils Dataset:Click Here

To Visit My Previous Different Types Of Series 2025 Dataset:Click Here

To Visit My Previous Analyzing Yoga Asanas Worldwide Dataset:Click Here

To Visit My Previous Analyzing Indian Languages Dataset:Click Here  

Follow Us On : 

 

---> FOLLOW OUR BLOG FOR MORE INFORMATION.

--->PLEASE DO COMMENTS AND SHARE OUR BLOG.