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 |
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?
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;
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)
;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.