Hi Guys,
using Proc sql how to find top Nth Highest salaries for each department wise even if same salary respective departments
PROC RANK would be a great tool to achieve this:
data have;
input dpt $ employeeID salary;
datalines;
A 1 100
A 2 110
A 3 90
B 1 120
B 2 70
B 3 60
B 4 130
;
run;
proc rank data=have out=want (where=(top_salary<=2)) descending; /* instead of "2", put the top "n" that you want*/
by dpt;
var salary;
ranks top_salary;
run;
PROC RANK would be a great tool to achieve this:
data have;
input dpt $ employeeID salary;
datalines;
A 1 100
A 2 110
A 3 90
B 1 120
B 2 70
B 3 60
B 4 130
;
run;
proc rank data=have out=want (where=(top_salary<=2)) descending; /* instead of "2", put the top "n" that you want*/
by dpt;
var salary;
ranks top_salary;
run;
As an alternative, and especially if you have a huge dataset, a HASH can be useful:
data have;
input dpt $ employeeID salary;
datalines;
A 1 100
A 2 110
A 3 90
B 1 120
B 2 70
B 3 60
B 4 130
;
run;
data want;
declare hash h(multidata: 'y', ordered: 'd');
h.definekey('salary');
h.definedata('dpt', 'employeeID', 'salary');
h.definedone();
declare hiter C('h');
do until(last.dpt);
set have;
by dpt;
h.add();
end;
C.first();
do i=1 to 2; /*instead of 2, put the top'n' that you want*/
output;
C.next();
end;
drop i;
run;
I want proc sql query not proc rank and hash table
"Saying thank you is more than good manners. it is good spirituality"
@BrahmanandaRao wrote:
I want proc sql query not proc rank and hash table
Don't be stupid, use the right tool.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.