Calcite | Level 5

## Use data step to get the second highest salary

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                 |
+---------------------+```

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Use data step to get the second highest salary

``````
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;``````
7 REPLIES 7
Amethyst | Level 16

## Re: Use data step to get the second highest salary

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

proc sql;
select max(salary) from have;
quit;``````
Thanks,
Jag
SAS Employee

## Re: Use data step to get the second highest salary

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;``````

Pyrite | Level 9

## Re: Use data step to get the second highest salary

``````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;``````
Tourmaline | Level 20

## Re: Use data step to get the second highest salary

@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;

Tourmaline | Level 20

## Re: Use data step to get the second highest salary

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

## Re: Use data step to get the second highest salary

``````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``````
Tourmaline | Level 20

## Re: Use data step to get the second highest salary

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;``````
Discussion stats
• 7 replies
• 11967 views
• 4 likes
• 6 in conversation