turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- How Do I Sum a column that is a calculated by two ...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2016 10:10 PM

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

Accepted Solutions

Solution

05-12-2016
02:39 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DME790

05-12-2016 02:35 AM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DME790

05-12-2016 12:56 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JerryLeBreton

05-12-2016 01:45 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DME790

05-12-2016 02:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JerryLeBreton

05-12-2016 02:39 AM

Thanks heaps Jerry,

worked a treat.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DME790

05-12-2016 02:52 AM

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)