using proc sql Department wise highest salary gt 10000

[aanan1417]

Department wise highest salary gt 10000 using proc sql

data a;

input name salary department;

datalines;

rahul 100000   a

rohit  90900     b

nisha 8000      a

pulkit 7000      b

yashika 6000  a

yash 11000     b

;

run;

[LinusH]

Take a look at HAVING and MAX() aggregate function.

[aanan1417]

Hi Sir,

Could you pls help me with the code

Best Regards

Rahul

[Kurt_Bremser]

First, fix this:

 72         
 73         data a;
 74         input name salary department;
 75         datalines;
 
 NOTE: Invalid data for name in line 76 1-5.
 NOTE: Invalid data for department in line 76 16-16.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 76         rahul 100000   a
 name=. salary=100000 department=. _ERROR_=1 _N_=1
 NOTE: Invalid data for name in line 77 1-5.
 NOTE: Invalid data for department in line 77 18-18.
 77         rohit  90900     b
 name=. salary=90900 department=. _ERROR_=1 _N_=2
 NOTE: Invalid data for name in line 78 1-5.
 NOTE: Invalid data for department in line 78 17-17.
 78         nisha 8000      a
 name=. salary=8000 department=. _ERROR_=1 _N_=3
 NOTE: Invalid data for name in line 79 1-6.
 NOTE: Invalid data for department in line 79 18-18.
 79         pulkit 7000      b
 name=. salary=7000 department=. _ERROR_=1 _N_=4
 NOTE: Invalid data for name in line 80 1-7.
 NOTE: Invalid data for department in line 80 15-15.
 80         yashika 6000  a
 name=. salary=6000 department=. _ERROR_=1 _N_=5
 NOTE: Invalid data for name in line 81 1-4.
 NOTE: Invalid data for department in line 81 16-16.
 81         yash 11000     b
 name=. salary=11000 department=. _ERROR_=1 _N_=6
 NOTE: The data set WORK.A has 6 observations and 3 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds

[aanan1417]

data a; input name $ salary department $; datalines; rahul 100000 a rohit 90900 b nisha 8000 a pulkit 7000 b yashika 6000 a yash 11000 b ; run;

[Kurt_Bremser · Accepted solution]

From that, you can now use HAVING and MAX, as already suggested:

proc sql;
create table want as
  select *
  from a
  group by department
  having salary = max(salary)
;
quit;

[tarheel13]

data a;
input name $ salary department $;
datalines;
rahul 100000 a
rohit 90900 b
nisha 8000 a
pulkit 7000 b
yashika 6000 a
yash 11000 b
;
run;

proc sql number;
	select department, max(salary) as highest_sal
		from a
		group by department 
		having highest_sal gt 10000;
quit;

[aanan1417]

we need those salary which is greater than 10000.

Thanks in advance

[Kurt_Bremser]

To limit the salaries, use a dataset option:

  from a (where=(salary gt 10000))

which will greatly reduce the effort SQL has to spend on sorting.