error select and having statements

[Rsadiq]

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

[collinelliot]

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;

[novinosrin · Accepted solution]

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;

[Rsadiq]

Thank you I got it solved!

[novinosrin]

You are welcome! have a nice day