topic error select and having statements in SAS Enterprise Guide

error select and having statements

Rsadiq — Tue, 09 May 2017 14:48:30 GMT

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

Re: error select and having statements

collinelliot — Tue, 09 May 2017 14:56:19 GMT

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;

Re: error select and having statements

novinosrin — Tue, 09 May 2017 14:57:47 GMT

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;

Re: error select and having statements

Rsadiq — Tue, 09 May 2017 15:49:11 GMT

Thank you I got it solved!

Re: error select and having statements

novinosrin — Tue, 09 May 2017 16:24:25 GMT

You are welcome! have a nice day