BookmarkSubscribeRSS Feed
BrahmanandaRao
Lapis Lazuli | Level 10
proc sql;
select sex,age,count(*) as max_agecnt 
from sashelp.class
group by age
having max(age)>=1;
quit;

how to get maximum age count only using proc sql

6 REPLIES 6
Patrick
Opal | Level 21

having max(age)=age

BrahmanandaRao
Lapis Lazuli | Level 10

Hi Patrick

i am not getting required output i want only max age count 

whymath
Lapis Lazuli | Level 10

You are querying an anwser(how many records have max age) based on another anwser(what is the max age), so inline view is needed.

proc sql;
  select sex, age as max_age, max_age_cnt
  from (
    select sex, age, count(1) as max_age_cnt
    from sashelp.class
    group by sex, age
  )
  group by sex
  having max(age)=age
  ;
quit;

The inline view(a quoted query followed "from") anwsers the first question: How many records under different sex and age combinations.

The out query anwsers the second question: In these combinations, which has the maximum age for different sex?

whymath
Lapis Lazuli | Level 10

And you can have more straight thought:

Query 1: What is the max age under different sex?

Query 2: How many records have the max age under different sex?

select distinct a.*, count(b.name) as max_age_cnt
from (
  select sex, max(age) as max_age
  from sashelp.class
  group by sex
) as a
left join sashelp.class as b on a.sex=b.sex and a.max_age=b.age
group by a.sex
Pavani_GVL
Calcite | Level 5

Hi,

Here is one simple query :

 

proc sql;
select * from
(select sex,age,count(age) as age_cnt
from age_ds
group by sex,age) a
group by sex
having a.age_cnt=max(a.age_cnt)
;
quit;
Tom
Super User Tom
Super User

You have to first define what "max age count" means.

Is that the number of cases with the maximum age?

Perhaps you want one of these answers?

proc sql;
select age,count 
  from (select age,count(*) as count from sashelp.class group by age) 
  having age=max(age)
;
select max_age,sum(age = max_age)
  from (select age,max(age) as max_age from sashelp.class) 
  group by max_age
;
select sex,max_age,sum(age = max_age)
  from (select sex,age,max(age) as max_age from sashelp.class)
  group by sex,max_age 
;
select sex,max_age,sum(age = max_age)
  from (select sex,age,max(age) as max_age from sashelp.class group by sex)
  group by sex,max_age
;
quit;

Tom_1-1685369803180.png

 

Is that the age with the maximum number of cases?

Perhaps you want one of these answers.

proc sql;
 select age,count from (select age,count(*) as count from sashelp.class group by age)
   having count=max(count)
 ;
 select sex,age,count from (select sex,age,count(*) as count from sashelp.class group by sex,age)
   having count=max(count)
 ;
 select sex,age,count from (select sex,age,count(*) as count from sashelp.class group by sex,age)
   group by sex
   having count=max(count)
 ;
quit;
 

Tom_2-1685369880410.png

 

 

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

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
  • 6 replies
  • 937 views
  • 2 likes
  • 5 in conversation