Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-24-2015 02:05 AM
Regular Contributor
Posts: 161

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

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

 

Hope that explains.

 

Good Luck...!!!

Kannan Deivasigamani

View solution in original post


All Replies
Contributor
Posts: 26

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

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;

Regular Contributor
Posts: 161

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

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
Kannan Deivasigamani
Solution
‎11-24-2015 02:05 AM
Regular Contributor
Posts: 161

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

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

 

Hope that explains.

 

Good Luck...!!!

Kannan Deivasigamani
Respected Advisor
Posts: 3,896

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

proc sql;
  select *
  from sashelp.class
  having min(age)=age
  ;
quit;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 339 views
  • 0 likes
  • 4 in conversation