topic Re: Can any one help Why the error is coming? in SAS Data Science

PROC SQL returns ERROR: Summary functions are restricted to the SELECT and HAVING

devarayalu — Thu, 06 Jul 2017 18:57:55 GMT

194 proc sql;

195 select id, sum(no) as Total

196 from one

197 where calculated total > 100

198 group by id;

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

199 quit;

u can find the one dataset in the attachment.

Re: Can any one help Why the error is coming?

Patrick — Tue, 31 Jul 2012 08:11:57 GMT

That's how it should work:

proc sql;

select id, sum(no) as Total

from one

group by id

having sum(no)> 100

;

quit;

The "where" clause gets executed before the "group by" but you would need the summed variable which only can be created as part of the grouping already in the where clause. That can't work and that's why the ERROR is telling you that you can't use a summary function in the where clause (that's where you try to use the calculated variable).

Re: Can any one help Why the error is coming?

antony_allianz — Tue, 31 Jul 2012 10:48:42 GMT

If we do any aggregate function we can't implement any conditions using where clause. So that we use having clause to implement the condition.

if you assign a variable for the resultant value of aggregate function you have to use calculated in its condition part other wise you use the aggregate function in condition.The following coding is correct. Test it...

Proc sql;

Select id,sum(credit) from department group by id having sum(credit)>100;

Quit;

Re: Can any one help Why the error is coming?

yaswanthj — Tue, 31 Jul 2012 11:27:23 GMT

do not use where before "group by". can not put "where" for summurized conditions.

Please try this...

proc sql;

select id, sum(no) as Total from one

group by id

having calculated total > 100;

quit;

Re: Can any one help Why the error is coming?

lacrefa — Tue, 31 Jul 2012 12:20:39 GMT

The "calculated" will be used only when you use a calculated member (variable), not a aggregation. To verify, you can used SAS Enterprise Guide. In your case it is not used, because having already say aggregation variable.

Best regards

Re: Can any one help Why the error is coming?

lacrefa — Tue, 31 Jul 2012 12:31:30 GMT

Sorry an error , from the original message its is only the () were missing

PROC SQL;

SELECT DISTINCT t1.No,

(COUNT(t1.Arm_Id)) AS COUNT_of_Arm_Id

FROM WORK.TWO AS t1

GROUP BY t1.No

HAVING (CALCULATED COUNT_of_Arm_Id) > 1;

QUIT;