BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BrahmanandaRao
Lapis Lazuli | Level 10

Hi Guys,

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

Hi @BrahmanandaRao 

 

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;

View solution in original post

5 REPLIES 5
ed_sas_member
Meteorite | Level 14

Hi @BrahmanandaRao 

 

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
Meteorite | Level 14

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;
BrahmanandaRao
Lapis Lazuli | Level 10

I want proc sql query not proc rank and hash table

ed_sas_member
Meteorite | Level 14

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1079 views
  • 0 likes
  • 3 in conversation