BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
devarayalu
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
antony_allianz
Fluorite | Level 6

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

5 REPLIES 5
Patrick
Opal | Level 21

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

antony_allianz
Fluorite | Level 6

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;

yaswanthj
Calcite | Level 5

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;

lacrefa
Calcite | Level 5

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

lacrefa
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

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.

Discussion stats
  • 5 replies
  • 16698 views
  • 2 likes
  • 5 in conversation