DATA Step, Macro, Functions and more

Proc SQL Having Clause

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

Proc SQL Having Clause

Hi Guys,

Don't judge me, but I don't use SQL very often.  I am sure this is very simple but all I want to do is find the maximum of all conditions in a HAVING clause.  So for example if I am using the the SASHELP.CLASS dataset (I am only using this because it highlights what I am attempting to do) I would like to know which student is both the heaviest and the oldest for each sex.  If they aren't the the oldest AND heaviest I want them excluded.

I thought that the max function and grouping would do this but I failed.

PROC SQL;

CREATE TABLE HAVE AS

SELECT *

FROM SASHELP.CLASS

GROUP BY SEX

HAVING AGE = MAX(AGE) AND WEIGHT = MAX(WEIGHT)

;

QUIT;

All I want is to output Philip as he is both the oldest and the heaviest.

I know other ways to do this, but the code is already in SQL and it has to stay that way.

Can someone please help me out?

Thanks in advance.


Accepted Solutions
Solution
‎08-21-2014 11:16 AM
Respected Advisor
Posts: 4,654

Re: Proc SQL Having Clause

Please consider STAT@SAS comment seriously. In a general case your query will sometimes return nothing, as both conditions will not occur for the same individual.

It might be better to ask for the heaviest individual of the oldest group which would be given by

PROC SQL;

SELECT *

FROM (select * from SASHELP.CLASS having age = max(age))

HAVING WEIGHT = MAX(WEIGHT);;

QUIT;

PG

PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,131

Re: Proc SQL Having Clause

i believe the code you provided already outputs the heaviest and oldest by sex, if you wish to get the output with oldest and heaviest irrespective of sex then you can try

PROC SQL;

create table have2 as select * from sashelp.class HAVING AGE = MAX(AGE) AND WEIGHT = MAX(WEIGHT);

QUIT;

thanks,

Jag

Thanks,
Jag
Frequent Contributor
Posts: 117

Re: Proc SQL Having Clause

You should exclude group by clause as Jag told...because when you use a GROUP BY clause by sex variable that means it's going to split the data into two groups and will check the condition's in two groups. So, you get a result for M and another for F.

Super User
Posts: 5,260

Re: Proc SQL Having Clause

Keep the group by, Scott states "...for each sex".

Data never sleeps
Trusted Advisor
Posts: 1,204

Re: Proc SQL Having Clause

It's hard to implement two conditions at the same time. As height and weight are two random variables and there is no exact relationship between them.If we see female students Janet has maximum weight but not maximum height. On the other hand, in male students Philip has maximum height and maximum weight.

Frequent Contributor
Posts: 89

Re: Proc SQL Having Clause

euuh, this code is doing the job . . . no?

proc sql;

select *

from sashelp.class

having age=max(age) and weight=max(weight);

quit;

Solution
‎08-21-2014 11:16 AM
Respected Advisor
Posts: 4,654

Re: Proc SQL Having Clause

Please consider STAT@SAS comment seriously. In a general case your query will sometimes return nothing, as both conditions will not occur for the same individual.

It might be better to ask for the heaviest individual of the oldest group which would be given by

PROC SQL;

SELECT *

FROM (select * from SASHELP.CLASS having age = max(age))

HAVING WEIGHT = MAX(WEIGHT);;

QUIT;

PG

PG
PROC Star
Posts: 253

Re: Proc SQL Having Clause

This also works

proc sql;

select name, sex, age,height

from sashelp.class

where age in (select max(age)

from sashelp.class)

and height in(select max(height)

from sashelp.class)

;

quit;

New Contributor
Posts: 2

Re: Proc SQL Having Clause

Hi Scott,

While I echo the concerns of Sata@SAS and PGStats, I would like to know why you want only Philip in your query. It seems that Janet also qualifies as she is (one of) the oldest while being the heaviest. Are you looking for there to be a unique oldest (i.e. Janet does not qualify because Mary is also 15 and therefore there really isn't a single oldest)? My understanding of your requirement of  "which student is both the heaviest and the oldest for each sex" would include both Janet and Philip. Maybe some context to your real query might help us understand your requirement.

Best,

Joe

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 4784 views
  • 6 likes
  • 9 in conversation