topic Re: PROC SQL Subqueries in SAS Programming

PROC SQL Subqueries

Anita_n — Tue, 02 Feb 2021 21:49:55 GMT

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

Re: PROC SQL Subqueries

Reeza — Tue, 02 Feb 2021 22:02:05 GMT

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

Re: PROC SQL Subqueries

Anita_n — Tue, 02 Feb 2021 22:05:29 GMT

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

Re: PROC SQL Subqueries

PGStats — Tue, 02 Feb 2021 22:41:57 GMT

and you should replace

CATS(firstname||" "||lastname)

with

CATX(" ", firstname, lastname)

Re: PROC SQL Subqueries

Anita_n — Wed, 03 Feb 2021 07:55:43 GMT

Thanks, it worked fine