PROC SQL Subqueries

[Anita_n]

Dear all,

I have a table consisting of a list of females and males as follows:

I wish to use the below subquery to get out the first and last name in one column for

for the female with the minimum age. But am not getting it working. I will appreciate

any help. Thanks

this code works fine for the minimum age for both sex

PROC SQL;
SELECT lastname,firstname, age,
CATS(firstname||" "||lastname) AS Name FROM mytable
WHERE age=(SELECT MIN(age) FROM mytable) ;
QUIT;

 but I want to add to this code, that the minimum age should not apply to both sex but only the females.

PROC SQL;
SELECT lastname,firstname, age,
CATS(firstname||" "||lastname) AS Name FROM mytable
WHERE sex= "female" AND age=(SELECT MIN(age) FROM mytable) ;
QUIT;

if I add this part with sex="female" I get no results

[Reeza · Accepted solution]

Does this work for you? Your previous query was checking for women with the smallest age - but the smallest age was from the whole population which may not exist, therefore you get no records returned.

PROC SQL;
SELECT lastname,firstname, age,
CATS(firstname||" "||lastname) AS Name FROM mytable
WHERE sex= "female" AND age=(SELECT MIN(age) FROM mytable where sex='female') ;
QUIT;

Do you have to use a subquery? A HAVING clause is easier to code and understand.

PROC SQL;
SELECT lastname,firstname, age,
CATS(firstname||" "||lastname) AS Name FROM mytable
WHERE sex= "female" 
having age=min(age) ;
QUIT;

---

@Anita_n wrote:

Dear all,

I have a table consisting of a list of females and males as follows:

I wish to use the below subquery to get out the first and last name in one column for

for the female with the minimum age. But am not getting it working. I will appreciate

any help. Thanks

this code works fine for the minimum age for both sex

PROC SQL;
SELECT lastname,firstname, age,
CATS(firstname||" "||lastname) AS Name FROM mytable
WHERE age=(SELECT MIN(age) FROM mytable) ;
QUIT;

 but I want to add to this code, that the minimum age should not apply to both sex but only the females.

PROC SQL;
SELECT lastname,firstname, age,
CATS(firstname||" "||lastname) AS Name FROM mytable
WHERE sex= "female" AND age=(SELECT MIN(age) FROM mytable) ;
QUIT;

if I add this part with sex="female" I get no results

---

[Anita_n]

Okay thanks for the quick reply, I need to test that. I will leave a feedback

[PGStats]

and you should replace

CATS(firstname||" "||lastname)

with

CATX(" ", firstname, lastname)

[Anita_n]

Thanks, it worked fine