Desktop productivity for business analysts and programmers

error select and having statements

Accepted Solution Solved
Reply
Contributor
Posts: 25
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
PROC Star
Posts: 276

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

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

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;

Contributor
Posts: 25

Re: error select and having statements

Thank you I got it solved!

PROC Star
Posts: 276

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
  • 150 views
  • 0 likes
  • 3 in conversation