top N th highest sal

[Anandkvn]

Hi Guys,

using Proc sql  how to find top Nth Highest salaries for each department wise even if same salary respective departments 

[ed_sas_member · Accepted solution]

Hi @Anandkvn 

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;

[ed_sas_member]

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;

[Anandkvn]

I want proc sql query not proc rank and hash table

[ed_sas_member]

"Saying thank you is more than good manners. it is good spirituality"

[Kurt_Bremser]

---

@Anandkvn wrote:

I want proc sql query not proc rank and hash table

---

Don't be stupid, use the right tool.