Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

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

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

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

[ Edited ]

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.

Attachment

Accepted Solutions
Solution
‎07-06-2017 02:55 PM
Occasional Contributor
Posts: 9

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,887

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

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).

Solution
‎07-06-2017 02:55 PM
Occasional Contributor
Posts: 9

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

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;

Contributor
Posts: 70

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

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;

Contributor
Posts: 22

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

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

Contributor
Posts: 22

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 876 views
  • 0 likes
  • 5 in conversation