DATA Step, Macro, Functions and more

getting max value of count

Reply
Occasional Contributor
Posts: 12

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: 19,770

Re: getting max value of count

Posted in reply to soham_sas

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?  

 

 

Occasional Contributor
Posts: 12

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 ..


##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Posts: 19,770

Re: getting max value of count

Posted in reply to soham_sas

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
Super User
Posts: 7,039

Re: getting max value of count

[ Edited ]
Posted in reply to soham_sas

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: 298

Re: getting max value of count

Posted in reply to soham_sas

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;
Ask a Question
Discussion stats
  • 5 replies
  • 296 views
  • 0 likes
  • 4 in conversation