- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 | +---------------------+
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;input Id salary ;
cards;
1 100
2 200
3 100
;
proc sql;
select max(salary) from have;
quit;
Jag
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;