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.
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;
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).
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;
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;
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.