topic Re: finding maximum salary in SAS Programming

finding maximum salary

Aexor — Sun, 01 May 2022 19:03:22 GMT

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

Re: finding maximum salary

sbxkoenk — Sun, 01 May 2022 21:05:44 GMT

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

Re: finding maximum salary

mkeintz — Sun, 01 May 2022 22:34:08 GMT

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.