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