Contributor
Posts: 49

# getting max value of count

hi

i have an employee table with emp_ID , salary and dept ,
i want the dept where there are more employee having the salary of more than Rs 2450

below is that table

 empno ename sal deptno 7499 ALLEN 1600 30 7521 WARD 1250 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7698 BLAKE 2850 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7844 TURNER 1500 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 10
Super User
Posts: 23,689

## Re: getting max value of count

Your question doesn't appear to match your subject line ...can you explain your question more? Is it total amount per dept greater than the amount?

Are you using a data step or proc SQL?

Contributor
Posts: 49

## Re: getting max value of count

Hi Reeza

i am using a PROC SQL ..

The requirement is , we want the deptno in which the employee count is more
having salary of more than 2450 ..

attachments. -##
Super User
Posts: 23,689

## Re: getting max value of count

I think you mean sum, not count? Either way it's a similar idea, group by department and filter using a having clause.

``````Proc SQL;
Create table want as
Select dept
From have
Group by dept
Having sum(amt)> 2450;
Quit;``````
Super User
Posts: 8,081

## Re: getting max value of count

[ Edited ]

Get the count first and then find the max.

``````select deptno
from
(select deptno,count(*) as count
from HAVE
where sal > 2450
group by 1
)
having count=max(count)
;``````
Super Contributor
Posts: 324

## Re: getting max value of count

You have got SQL solutions.

Here is data step approach. This could be the fast way for a data set having millions of employees.

An array is used for Sorting, Counting and Lookups - all in one data step !

You may test and disprove me if I am wrong.

``````data _null_;
array k[10:30] _temporary_;
set have end = last;
if sal > 2450 then k[deptno] + 1;
if last then do;
max = 0;
do i = 10 to 30;
if not missing(k[i]) and k[i] > max then do;
max = k[i];
deptno = i;
end;
end;
put max= deptno =;
end;
run;``````
Discussion stats
• 5 replies
• 368 views
• 0 likes
• 4 in conversation