topic Re: top N th highest sal in SAS Procedures

top N th highest sal

BrahmanandaRao — Sat, 07 Mar 2020 08:31:36 GMT

Hi Guys,

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

Re: top N th highest sal

ed_sas_member — Sat, 07 Mar 2020 09:36:06 GMT

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;

Re: top N th highest sal

ed_sas_member — Sat, 07 Mar 2020 09:37:02 GMT

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;

Re: top N th highest sal

BrahmanandaRao — Sat, 07 Mar 2020 09:41:13 GMT

I want proc sql query not proc rank and hash table

Re: top N th highest sal

ed_sas_member — Sat, 07 Mar 2020 09:59:59 GMT

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

Re: top N th highest sal

Kurt_Bremser — Sat, 07 Mar 2020 10:07:05 GMT

@BrahmanandaRao wrote:

I want proc sql query not proc rank and hash table

Don't be stupid, use the right tool.