DATA Step, Macro, Functions and more

Sum and Group by

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

Sum and Group by

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
PROC Star
Posts: 547

Re: Sum and Group by

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;

View solution in original post


All Replies
Solution
a week ago
PROC Star
Posts: 547

Re: Sum and Group by

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;
Super User
Super User
Posts: 7,392

Re: Sum and Group by

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 71 views
  • 0 likes
  • 3 in conversation