Proc SQL Having Clause

[Scott_Mitchell]

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.

[Jagadishkatam]

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

[Vish33]

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.

[LinusH]

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

[stat_sas]

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.

[Jaheuk]

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

proc sql;

select *

from sashelp.class

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

quit;

[PGStats · Accepted solution]

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

[kiranv_]

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;

[joe00033]

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