Desktop productivity for business analysts and programmers

error select and having statements

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

error select and having statements

Hello

I am not sure what is causing this error

 

ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

 

Proc sql;

               Create table visit_count as

               select distinct

               a.ayb_id,

               a.VisitCnt as Visits

               ,sum(VisitCnt) as sum_visits

               from aybdata.portal_reg as a

               where calculated sum_visits >= 2

               group by a.ayb_id, a.VisitCnt

               order by a.ayb_id;

               quit;

 

Thanks

 

Rida


Accepted Solutions
Solution
‎05-09-2017 11:42 AM
Frequent Contributor
Posts: 107

Re: error select and having statements

Try:

Proc sql;

               Create table visit_count as

               select distinct

               a.ayb_id,

               a.VisitCnt as Visits

               ,sum(VisitCnt) as sum_visits

               from aybdata.portal_reg as a

               group by a.ayb_id, a.VisitCnt

               having sum_visits >= 2

               order by a.ayb_id;

               quit;

View solution in original post


All Replies
Super Contributor
Posts: 284

Re: error select and having statements

[ Edited ]

I removed the table alias since you're only using on table in the query. The subsetting you want to do is via a "having" statement that comes after your group by. 

 

Proc sql;
               Create table visit_count as
               select distinct ayb_id,
                         VisitCnt as Visits,
                        sum(VisitCnt) as sum_visits
               from aybdata.portal_reg
               group by ayb_id, VisitCnt
               having sum(VisitCnt) > 1;
               quit;
Solution
‎05-09-2017 11:42 AM
Frequent Contributor
Posts: 107

Re: error select and having statements

Try:

Proc sql;

               Create table visit_count as

               select distinct

               a.ayb_id,

               a.VisitCnt as Visits

               ,sum(VisitCnt) as sum_visits

               from aybdata.portal_reg as a

               group by a.ayb_id, a.VisitCnt

               having sum_visits >= 2

               order by a.ayb_id;

               quit;

Occasional Contributor
Posts: 19

Re: error select and having statements

Thank you I got it solved!

Frequent Contributor
Posts: 107

Re: error select and having statements

You are welcome! have a nice day

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 128 views
  • 0 likes
  • 3 in conversation