Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

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

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-021,4697043357:59
2016-05-031,8638061047:13
2016-05-041,9038003307:01
2016-05-051,9588066116:52
2016-05-061,8677259756:29
2016-05-07153635626:55
2016-05-08103425296:53
2016-05-096,43425930796:43
2016-05-107,01825134935:58
2016-05-116,27322386155: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


Accepted Solutions
Solution
‎05-12-2016 02:39 AM
Frequent Contributor
Posts: 85

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

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  Smiley Happy

View solution in original post


All Replies
Frequent Contributor
Posts: 85

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

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;
Contributor
Posts: 70

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

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

 

 

Solution
‎05-12-2016 02:39 AM
Frequent Contributor
Posts: 85

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

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  Smiley Happy

Contributor
Posts: 70

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

Thanks heaps Jerry,

 

worked a treat.

Frequent Contributor
Posts: 85

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

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)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 580 views
  • 2 likes
  • 2 in conversation