How to find out third largest/ smallest number in dataset?

[ImPrem]

Hi all,

I'm a student learning Base SAS Programming. My trainer gave me an assignment to find out the largest or smallest number from dataset. 

It is easy in excel, we can use "large" function to find out the same. I don't have any clue on Base SAS. Kindly help me out. 

Ex:

Data EmployeeSalary;

input empid $ name $ salary comma6. ;

cards;

RY4672 arun 57,899

TU5789 Varun 24,769

YI5628 Vikas 68,588

GU5879 Ajay 38,589

FL5728 Shiva 42,699

run;

[art297]

Take a look at http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002154865.htm and

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002154862.htm

Art, CEO, AnalystFinder.com

[ImPrem]

Thank you very much for your quick response. 

If you don't mind, kindly help me out with code. 

[art297]

There is more than one answer. Here is one way it could be used:

Data EmployeeSalary;
  input empid $ name $ salary comma6. ;
  cards;
RY4672 arun 57,899
TU5789 Varun 24,769
YI5628 Vikas 68,588
GU5879 Ajay 38,589
FL5728 Shiva 42,699
run;

data want (keep=salaries third_smallest);
  set EmployeeSalary end=eof;
  array salaries(9999) _temporary_;
  salaries(_n_)=salary;
  if eof then do;
    third_smallest=smallest(3,of salaries(*));
    output;
  end;
run;

Art, CEO, AnalystFinder.com

[ImPrem]

I'm a begineer (Base SAS). For me, its very difficult to understand the sas code. I heard SAS is very simple tool when compare to Excel. But your code was very difficult to understand. Kindly give me very simple SAS code. 

Output needs to be printed using proc print. 

Thanks in advance!!! 

[art297]

Sorry, I don't know of an easy way to do what you want. The code I suggested put all of the salaries in an array and then found the third smallest one. You can use proc print to print the resulting file/answer

An alternative, but more complex, is to transpose the file. That is, put all of the salaries on one line. e.g.:

Data EmployeeSalary;
  input empid $ name $ salary comma6. ;
  cards;
RY4672 arun 57,899
TU5789 Varun 24,769
YI5628 Vikas 68,588
GU5879 Ajay 38,589
FL5728 Shiva 42,699
run;

proc transpose data=EmployeeSalary out=wide;
  var salary;
run;

data want(keep=third_smallest);
  set wide;
  third_smallest=smallest(3,of col:);
run;

proc print data=want;
run;

Art, CEO, AnalystFinder.com

[ImPrem]

Superb!! Your SAS code is very simple & easy to learn for begineers like me. 

Output suppose to print the entire coloumn. I mean "Name Empid & Salary"

As well as if replace smallest to largest syntax code is not executing.. help me out. 

Thanks in advance!

[Ksharp · Accepted solution]

@art297 ,

Why not use PROC SORT and pick up the third obs , proc transpose is not efficient .

BTW, Congratulations! you surpass @Reeza ,become No.1 solution sas user.

Since you both come from Canada, Have you both met before ?

[ImPrem]

Wooow. Very simple & superb technique!!! Thanks! 

[thisisneeraj]

Hi All,

This can be easiest solution to find third lowest number in dataset.

DATA XY;
INPUT Dept $ Salary;
datalines;
AA 100
AA 400
AA 200
AA 300
BB 222
BB 111
BB 444
BB 333
;
RUN;
PROC SORT DATA=XY ;
BY Dept Salary;RUN;

data xy1;
set XY;
by Dept Salary;
if first.dept=1 then n=0;
n+1;
if n=3 then output;
run;

hope this helps.

I am working upon to write the code for third largest as well.

guess, proc sql can do this for third largest easily.

[ImPrem]

Your sas code wont execute the expected output. 

Here you go... 

proc sort data=EmployeeSalary out=emp1;
by descending sal;  / * keep "by sal"  - lowest 3rd Value /
run;
proc print data=emp1 (firstobs=3 obs=3); 
run;

Very simple logic dude!!!! 🙂 

[thisisneeraj]

How would you find department wise third highest salary ? 

Your answer will not work in that case... 

Your answer is so much at basic level my friend ...!! 

[ImPrem]

My dear friend @thisisneeraj, Nobody want big copmplicated program to execute small thing. At the end, everyone is looking for final output. 

Your program is much compliacted (As per understandiing) to execute this very small logic.

As you raised the question, "How would you find department wise third highest salary ? "

Here you go.!!!! 

data abc;
input name $ dept $ sal comma6. loc $;
cards;
abc HR 32,392 CH
ghs MAR 26,378 MY
gsi HR 62,362 JU
gdi HR 23,379 JK
qim HR 83,368 UK
own HR 82,738 IK
KUW MAR 82,768 OK
;
proc sort data=abc out=xyz;
by dept descending sal;
run;
proc print data=xyz (firstobs=3 obs=3);
var name dept sal loc;
where dept= 'HR';
run;

All the best!!!! 

[thisisneeraj]

hi All,

Posting the finally completed answer: 

Lets prepare the data first: 

DATA XY;
INPUT Dept $ Salary;
datalines;
AA 100
AA 400
AA 200
AA 300
BB 222
BB 111
BB 444
BB 333
AA 500
AA 600
AA 700
BB 555
BB 666
BB 777
;
RUN;

1) For third smallest: 
PROC SORT DATA=XY ;
BY Dept Salary;RUN;

data ThirdSmallest;
set XY;
by Dept Salary;
if first.dept=1 then n=0;
n+1;
if n=3 then output;
run;

2) For third highest: 

PROC SORT DATA=XY ;BY Dept descending Salary;RUN;

PROC RANK DATA=XY descending OUT=XYRanked ;
var Salary; by dept; ranks SalaryRank;
run;

data thirdLargest;
set XYRanked;
if SalaryRank=3;
run;

hope this helps .

Thanks a lot .

Please let me know for any more inputs in this regards.

[art297]

@Ksharp: I didn't know we were having a contest but, no, I could never keep up with @Reeza. Yes, while we live almost 3,364 km apart, @Reeza and I have met at previous SGFs. As for the transpose solution, it wasn't ,my first choice! I only offered it because @ImPrem asked for an alternative to the one I actually presented.

However, since you mentioned efficiency, the code I originally proposed, runs almost 3 times faster than using sort. I ran a rather simple test:

data weight;
  do i=1 to 1000000;
    weight=ranuni(927);
    output;
  end;
run;

data want;
  set weight end=eof;
  array weights(1000000) _temporary_;
  weights(_n_)=weight;
  if eof then do;
    third_smallest=smallest(3,of weights(*));
    output;
  end;
run;

proc print data=want;
run;

proc sort data=weight out=temp;
by weight;
run;

proc print data=temp (firstobs=3 obs=3); 
run;

@ImPrem: Your final solution has a critical error in it. You sort by descending, thus put the records with the largest values at the top, but then print the 3rd largest and call it the third smallest. However, it was nice to see that you really do appear to be trying to learn SAS.

Art, CEO, AnalystFinder.com