topic Re: getting max value of count in SAS Programming

getting max value of count

soham_sas — Sat, 09 Jul 2016 19:14:32 GMT

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

Re: getting max value of count

Reeza — Sat, 09 Jul 2016 19:26:45 GMT

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?

Re: getting max value of count

soham_sas — Sat, 09 Jul 2016 21:08:56 GMT

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

Re: getting max value of count

Reeza — Sat, 09 Jul 2016 21:15:26 GMT

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;

Re: getting max value of count

Tom — Sat, 09 Jul 2016 21:28:21 GMT

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)
;

Re: getting max value of count

KachiM — Sat, 09 Jul 2016 22:36:36 GMT

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;