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
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Sum and Group by

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

a week ago

I've a data as follows.

ticket_count | resolved_by | percentage_of_tickets_resolved |

4 | L2 | 19.05% |

7 | L2 | 33.33% |

5 | L2 | 23.81% |

4 | L3 | 19.05% |

1 | L3 | 4.76% |

I just want to sum the variables ticket_count and percentage_of_tickets_resolved and group by the varaible resolved_by to display the the data as follows.

ticket_count | resolved_by | percentage_of_tickets_resolved |

16 | L2 | 76.19% |

5 | L3 | 23.81% |

When I tried the below code, I received the error as

```
26 proc sql;
27 select *,sum(ticket_count) as ticket_counts,sum(percentage_of_tickets_resolved) as t_percentage_of_tickets_resolved
28 from summary1
29 group by ticket_counts
30 having (calculated ticket_counts)>1;
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
31 quit;
```

I think there is an issue with having clause in my code. Appreciate if someone help me to overcome this issue.

Accepted Solutions

Solution

a week ago

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

a week ago

```
data have;
input ticket_count resolved_by$ percentage_of_tickets_resolved;
datalines;
4 L2 19.05
7 L2 33.33
5 L2 23.81
4 L3 19.05
1 L3 4.76
;
proc sql;
create table want as
select sum(ticket_count) as ticket_count_sum
,resolved_by
,sum(percentage_of_tickets_resolved) as percentage_of_tickets_resolved
from have
group by resolved_by
having ticket_count_sum > 1;
quit;
```

All Replies

Solution

a week ago

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

a week ago

```
data have;
input ticket_count resolved_by$ percentage_of_tickets_resolved;
datalines;
4 L2 19.05
7 L2 33.33
5 L2 23.81
4 L3 19.05
1 L3 4.76
;
proc sql;
create table want as
select sum(ticket_count) as ticket_count_sum
,resolved_by
,sum(percentage_of_tickets_resolved) as percentage_of_tickets_resolved
from have
group by resolved_by
having ticket_count_sum > 1;
quit;
```

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

a week ago

Have you tried replacing:

having (calculated ticket_counts)>1;

With:

having sum(ticket_count > 1;

Again, its highly advisable to add __ Test data in the form of a datastep__ so that we can check.