finding maximum salary

[Aexor]

Hi ,

I have below table. I need to find out employee names whose salary is greater than his manager 

emp_id  name   dept   sal   manager_id
1           A         HR     200          2
2          B         Fin     300            3
3         C         anaytics  400        4
4         D            QA       100        1

[sbxkoenk]

data have0;
LENGTH emp_id 8 name $ 1 dept $ 10 sal 8 manager_id 8;
input emp_id  name $  dept $  sal  manager_id;
datalines;
1       A      HR         200   2
2       B      Fin        300   3
3       C      analytics  400   4
4       D      QA         100   1
;
run;

PROC SQL noprint;
 create table have1 as
 select t1.* , t2.sal as sal_mgr
 from   work.have0                  as t1
      , work.have0(keep=emp_id sal) as t2
 where t1.manager_id = t2.emp_id
 order by t1.emp_id , t1.name
;
QUIT;

data want;
 set have1;
 where sal > sal_mgr;
run;
/* end of program */

Koen

[mkeintz]

A reasonable solution to this problem is a hash object:

data have0;
LENGTH emp_id 8 name $ 1 dept $ 10 sal 8 manager_id 8;
input emp_id  name $  dept $  sal  manager_id;
datalines;
1       A      HR         200   2
2       B      Fin        300   3
3       C      analytics  400   4
4       D      QA         100   1
;
run;


data want;
  set have0   have0 (obs=0 rename=(sal=_mgr_sal));
  if _n_=1 then do;    
    declare hash h (dataset:'have0 (keep=sal emp_id rename=(emp_id=manager_id sal=_mgr_sal)');
      h.definekey('manager_id');
      h.definedata('_mgr_sal');
      h.definedone();
  end;
  if h.find()=0 and sal>_mgr_sal;
run;

The reason for the second HAVE0 in the SET statement is just to prompt the SAS compiler to allocate a place in the program data vector for the variable _mgr_sal.  Mentioning it in the DECLARE statement is not sufficient.  But notice the second HAVE0 has "obs=0" so no extra data is generated.