Morning All,
Only new to SAS EG and I'm trying to caclulate a total at the bottom of a table. I can get the total for calls handled by using
using SAS 7.1
sum total_calls
I have a column that is calulated on the Total_Handle_Time/Total_Calls to create the column called AHT which is a daily average result.
How do I now get get the overall average result for the selected period?
EVENT_DT TOTAL_CALLS Total_Handle_Time AHT
2016-05-02 | 1,469 | 704335 | 7:59 |
2016-05-03 | 1,863 | 806104 | 7:13 |
2016-05-04 | 1,903 | 800330 | 7:01 |
2016-05-05 | 1,958 | 806611 | 6:52 |
2016-05-06 | 1,867 | 725975 | 6:29 |
2016-05-07 | 153 | 63562 | 6:55 |
2016-05-08 | 103 | 42529 | 6:53 |
2016-05-09 | 6,434 | 2593079 | 6:43 |
2016-05-10 | 7,018 | 2513493 | 5:58 |
2016-05-11 | 6,273 | 2238615 | 5:57 |
29,041 11294633
Data Report.AEC_Final;
Set Work.AEC_SUM;
Drop QUEUE ;
AHT = Total_Handle_Time/TOTAL_CALLS;
Format AHT mmss. Total_Calls Comma9.;
Run;
Proc PRINT data=Report.AEC_Final NOOBS;
sum total_calls Total_Handle_Time;
Run;
Any help appreciated
Cheers
To use, but not print the Total_Handle_Time column, you must leave it in the COL statement (to allow it to be used in the calculation of the computed column) but add the NOPRINT in the DEFINE statement. See below.
Not sure exactly what you need but you can also save the intermediate dataset the Proc REPORT creates, with the OUT= option, which might do you.
And if so, you can run the Proc Report step right after your first Data step, unless you want the other temporary datasets later, by making the event_dt column a GROUP variable.
So, your program could look like this...
Data Work.AEC;
Set CARTEL.Employee_Telephony_agg;
Keep Event_DT QUEUE_CD QUEUE TOTAL_CALLS Total_Handle_Time;
Where QUEUE_CD in ('NAT_AEC' 'NAT_AEI' 'NAT_BVL' 'CLK_AECa_AustElectoralCommissionFedElectionServ')
and EVENT_DT >='02May2016'd;
Total_Handle_Time = TALK_TIME + WORK_TIME + HOLD_TIME + EXTENDED_WORK_TIME;
Run;
proc report data=Work.AEC out=final_aec(drop=_break_) ;
col EVENT_DT TOTAL_CALLS Total_Handle_Time AHT;
define event_dt / GROUP;
define TOTAL_CALLS / analysis sum ;
define Total_Handle_Time / NOPRINT analysis sum format=comma12.;
define AHT / computed format=mmss.;
compute AHT ;
AHT = Total_Handle_Time.sum / TOTAL_CALLS.sum;
endcomp;
rbreak after / summarize;
run;
Guess you guys are pretty busy at the moment
You might be better venturing into the world of Proc REPORT - you can't do much with Proc PRINT.
This should do the trick, and you won't need a Data step either...
proc report data=Work.AEC;
col EVENT_DT TOTAL_CALLS Total_Handle_Time AHT;
define event_dt / display;
define TOTAL_CALLS / analysis sum ;
define Total_Handle_Time / analysis sum format=comma12.;
define AHT / computed format=mmss.;
compute AHT ;
AHT = Total_Handle_Time.sum / TOTAL_CALLS.sum;
endcomp;
rbreak after / summarize;
run;
Thanks Jerry,
I need to keep the data steps to get the data to the level I need (I think).
I also need to remove the Total_Handle_Time from the report. I have removed the
col EVENT_DT TOTAL_CALLS AHT;
line. and get the following message.
WARNING: Total_Handle_Time is not in the report definition.
NOTE: The computed variable AHT is also a data set variable.
NOTE: The output might not be as expected.
ERROR: The variable type of 'TOTAL_HANDLE_TIME.SUM'n is invalid in this context.
This is my full code
Data Work.AEC;
Set CARTEL.Employee_Telephony_agg;
Keep Event_DT QUEUE_CD QUEUE TOTAL_CALLS Total_Handle_Time;
Where QUEUE_CD in ('NAT_AEC' 'NAT_AEI' 'NAT_BVL' 'CLK_AECa_AustElectoralCommissionFedElectionServ')
and EVENT_DT >='02May2016'd;
Total_Handle_Time = TALK_TIME + WORK_TIME + HOLD_TIME + EXTENDED_WORK_TIME;
Run;
Proc Sort data=Work.AEC;
By Event_DT QUEUE_CD QUEUE;
Run;
Proc Summary data=Work.AEC Sum Noprint;
By Event_DT;
Var TOTAL_CALLS Total_Handle_Time;
Output out=Work.AEC_SUM (DROP=_FREQ_ _TYPE_) SUM=;
Run;
Data Report.AEC_Final;
Set Work.AEC_SUM;
Drop QUEUE Total_Handle_Time;
AHT = Total_Handle_Time/TOTAL_CALLS;
Format AHT mmss. Total_Calls Comma9.;
Run;
proc report data=Report.AEC_Final;
col EVENT_DT TOTAL_CALLS AHT;
define event_dt / display;
define TOTAL_CALLS / analysis sum ;
define Total_Handle_Time / analysis sum format=comma12.;
define AHT / computed format=mmss.;
compute AHT ;
AHT = Total_Handle_Time.sum / TOTAL_CALLS.sum;
endcomp;
rbreak after / summarize;
run;
your help is appreciated.
Cheers Dean
To use, but not print the Total_Handle_Time column, you must leave it in the COL statement (to allow it to be used in the calculation of the computed column) but add the NOPRINT in the DEFINE statement. See below.
Not sure exactly what you need but you can also save the intermediate dataset the Proc REPORT creates, with the OUT= option, which might do you.
And if so, you can run the Proc Report step right after your first Data step, unless you want the other temporary datasets later, by making the event_dt column a GROUP variable.
So, your program could look like this...
Data Work.AEC;
Set CARTEL.Employee_Telephony_agg;
Keep Event_DT QUEUE_CD QUEUE TOTAL_CALLS Total_Handle_Time;
Where QUEUE_CD in ('NAT_AEC' 'NAT_AEI' 'NAT_BVL' 'CLK_AECa_AustElectoralCommissionFedElectionServ')
and EVENT_DT >='02May2016'd;
Total_Handle_Time = TALK_TIME + WORK_TIME + HOLD_TIME + EXTENDED_WORK_TIME;
Run;
proc report data=Work.AEC out=final_aec(drop=_break_) ;
col EVENT_DT TOTAL_CALLS Total_Handle_Time AHT;
define event_dt / GROUP;
define TOTAL_CALLS / analysis sum ;
define Total_Handle_Time / NOPRINT analysis sum format=comma12.;
define AHT / computed format=mmss.;
compute AHT ;
AHT = Total_Handle_Time.sum / TOTAL_CALLS.sum;
endcomp;
rbreak after / summarize;
run;
Guess you guys are pretty busy at the moment
Thanks heaps Jerry,
worked a treat.
And just for fun, and improved performance, you should be able to do the whole job with PROC REPORT:
proc report data=CARTEL.Employee_Telephony_agg out=final_aec(drop=_break_) ;
where QUEUE_CD in ('NAT_AEC' 'NAT_AEI' 'NAT_BVL' 'CLK_AECa_AustElectoralCommissionFedElectionServ')
and EVENT_DT >='02May2016'd;
col EVENT_DT TOTAL_CALLS TALK_TIME WORK_TIME HOLD_TIME EXTENDED_WORK_TIME AHT;
define event_dt / group;
define TOTAL_CALLS / analysis sum ;
define TALK_TIME / noprint analysis sum ;
define WORK_TIME / noprint analysis sum ;
define HOLD_TIME / noprint analysis sum ;
define EXTENDED_WORK_TIME / noprint analysis sum ;
define AHT / computed format=mmss.;
compute AHT ;
AHT = sum(TALK_TIME.sum, WORK_TIME.sum, HOLD_TIME.sum, EXTENDED_WORK_TIME.sum) / TOTAL_CALLS.sum;
endcomp;
rbreak after / summarize;
run;
(Not tested)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.