BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ccnky123
Calcite | Level 5

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

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;

View solution in original post

7 REPLIES 7
Jagadishkatam
Amethyst | Level 16

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

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

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
Pyrite | Level 9
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
Tourmaline | Level 20

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

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
Lapis Lazuli | Level 10
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
Tourmaline | Level 20

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 11966 views
  • 4 likes
  • 6 in conversation