topic Want min of age from sashelp.class dataset using Proc SQL? in SAS Procedures

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

vandhan — Tue, 24 Nov 2015 06:30:24 GMT

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 — Tue, 24 Nov 2015 06:46:50 GMT

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 — Tue, 24 Nov 2015 06:55:03 GMT

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 — Tue, 24 Nov 2015 06:56:25 GMT

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

Hope that explains.

Good Luck...!!!

Patrick — Tue, 24 Nov 2015 06:58:34 GMT

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