topic Re: Sum and Group by in SAS Programming

Sum and Group by

RamKumar — Mon, 07 Aug 2017 13:08:19 GMT

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.

Re: Sum and Group by

PeterClemmensen — Mon, 07 Aug 2017 13:13:40 GMT

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;

Re: Sum and Group by

RW9 — Mon, 07 Aug 2017 13:16:19 GMT

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.