Knowledge Article View - Customer Support

An error might occur when you run an SQL procedure query when the following conditions exist:

An SQL view includes the creation of a new variable as a result of a summary function.
The view is used in a subsequent query where it is joined to another table using an outer join and the new variable is used in the GROUP BY clause.
The variable position in the SELECT clause rather than the variable name is used in the GROUP BY clause.

The error might look similar to the following:

ERROR: GROUP BY clause contains a summary function, which is not allowed. ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

Submitting a query similar to the following might result in the above error:

proc sql;
create view v2 as
select
x, y, max(z) as mz
from t2
group by x, y;
quit;

proc sql;
create table results as
select t1.x, sum(t1.y) as sy, v2.mz
from t1 left join v2
on t1.x=v2.x
group by 3;
quit;

The only workaround is to use the variable name rather than its position in the GROUP BY clause.

Using the position rather than the name of a variable in a GROUP BY clause might result in an error