Use data step to get the second highest salary

[ccnky123]

Use data step to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

[Jagadishkatam]

data have;input Id  salary ;
cards;
 1  100 
2  200
3  100
;

proc sql;
select max(salary) from have;
quit;

[Panagiotis]

In the following code I am:

1. Creating two test tables, one with multiple rows, the other with one.

2. Sorting both tables

3. Using a data step to get the second highest salary and store it in a macro variable

4. Not sure exactly what you want if there is only one row, but in this program it should create a data set with no rows and a note in the log. You can change it to whatever.

/*Create the table with multiple rows*/
data test;
	infile datalines;
	input ID $ Salary;
	datalines;
1 100
2 200
3 1111
4 500
5 700
6 1000
;

/*Create the table with only one row*/
data testoneobs;
	infile datalines;
	input ID $ Salary;
	datalines;
1 100
;

/*Sort by multiple rows table descending salary*/
proc sort data=test;
	by descending salary;
run;
/*Sort single row table descending salary*/
proc sort data=testoneobs;
	by descending salary;
run;


/*You can test this program with both tables from above*/
data secondhighest;
	set test end=lastobs;
	if lastobs and _n_=1 then putlog 'Only one salary';/*or whatever else you want*/
	else if _n_ =2 then do;
		call symputx('SecondSalary',salary);/*Store in macro variable, remove if you don't want*/
		output;
		stop;
	end;
run;
/*Check value of new macro variable*/
%put &=secondsalary;


/*Print report and use the macro variable in the title, or whatever else you need of it*/
title "Second highest salary is: &SecondSalary";
proc print data=secondhighest;
run;
title;

[rajeshalwayswel]

data have;
input Id Salary ;
cards;
1 100
2 200
3 30 
run;

proc sort data=have ;
by id descending salary;
run;

data want;
set have;
if _n_=2;
run;

[novinosrin]

@rajeshalwayswel  Your code is incorrect. Notice the below change in data, I request you to intuitively think why your logic is incorrect before you get help just to help solve

data have;
input Id Salary ;
cards;
1 100
2 50
3 200
run;

proc sort data=have ;
by id descending salary;
run;

data want;
set have;
if _n_=2;
run;

[novinosrin · Accepted solution]

data have;
input Id  salary ;
cards;
1  100 
2  200
3  300
;
proc rank data=have out=want(where=(ranksalary=2))  ties=low;

   var salary;
   ranks ranksalary;
run;

[BrahmanandaRao]

input Id  salary ;
cards;
1  100 
2  200
3  300
4 200
;
run;


Then output should be
2 200
4 200

How to solve it

[novinosrin]

Hi @BrahmanandaRao   It's the same code for your sample too. Test it plz

data have;
input Id  salary ;
cards;
1  100 
2  200
3  300
4 200
;
run;

proc rank data=have out=want(where=(ranksalary=2))  ties=low;
   var salary;
   ranks ranksalary;
run;