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
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
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
and you should replace
CATS(firstname||" "||lastname)
with
CATX(" ", firstname, lastname)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.