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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.