Want min of age from sashelp.class dataset using Proc SQL?

[vandhan]

Hi,

I wanted to find name of whose age is mininum in the sashelp.class dataset using proc sql.

I'm trying using below.

proc sql;

create table want as

select min(age), name from sashelp.class;

quit;

but is giving all 19 records with min 11 age on all records.

I want record/s with minimum age and their name.

In this case.. 11th and 18th records in class dataset having age is 11, which is minimum .

Please help.

[AbhiD]

One of the way is to use subquery in SQL

proc sql;
select
a.name,
a.age
from sashelp.class as a
where a.age in
(select min(age) from sashelp.class )
;

quit;

[kannand]

Try this:

proc sql;
create table want as
select name , age 
  from sashelp.class
 where age in (select min(age) 
 				 from sashelp.class); 

quit;

It gives 2 rows as they both have age 11 which is the least.

	
        Name    Age
1	Joyce	11
2	Thomas	11

[kannand · Accepted solution]

The reason your query returned 19 rows because you selected min (age) for each name. Hence the result.

Hope that explains.

Good Luck...!!!

[Patrick]

proc sql;
  select *
  from sashelp.class
  having min(age)=age
  ;
quit;