BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RamKumar
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
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

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20
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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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