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;
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;
Take a look at HAVING and MAX() aggregate function.
Hi Sir,
Could you pls help me with the code
Best Regards
Rahul
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
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;
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;
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;
we need those salary which is greater than 10000.
Thanks in advance
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.