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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.