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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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
Vish33
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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

Data never sleeps
stat_sas
Ammonite | Level 13

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
Obsidian | Level 7

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

proc sql;

select *

from sashelp.class

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

quit;

PGStats
Opal | Level 21

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
kiranv_
Rhodochrosite | Level 12

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
Calcite | Level 5

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

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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