BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vandhan
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
kannand
Lapis Lazuli | Level 10

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

4 REPLIES 4
AbhiD
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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
kannand
Lapis Lazuli | Level 10

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
Patrick
Opal | Level 21
proc sql;
  select *
  from sashelp.class
  having min(age)=age
  ;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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