topic Re: How Do I Sum a column that is a calculated by two other columns in SAS Enterprise Guide

How Do I Sum a column that is a calculated by two other columns

DME790 — Thu, 12 May 2016 02:10:30 GMT

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

Re: How Do I Sum a column that is a calculated by two other columns

JerryLeBreton — Thu, 12 May 2016 04:56:52 GMT

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;

Re: How Do I Sum a column that is a calculated by two other columns

DME790 — Thu, 12 May 2016 05:45:47 GMT

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

Re: How Do I Sum a column that is a calculated by two other columns

JerryLeBreton — Thu, 12 May 2016 06:35:54 GMT

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

Re: How Do I Sum a column that is a calculated by two other columns

DME790 — Thu, 12 May 2016 06:39:36 GMT

Thanks heaps Jerry,

worked a treat.

Re: How Do I Sum a column that is a calculated by two other columns

JerryLeBreton — Thu, 12 May 2016 06:52:54 GMT

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)