topic Re: How to find out third largest/ smallest number in dataset? in SAS Programming

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

ImPrem — Wed, 10 May 2017 16:14:43 GMT

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;

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

art297 — Wed, 10 May 2017 16:19:04 GMT

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

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

ImPrem — Wed, 10 May 2017 16:24:01 GMT

Thank you very much for your quick response.

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

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

art297 — Wed, 10 May 2017 16:44:15 GMT

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

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

ImPrem — Wed, 10 May 2017 16:51:37 GMT

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!!!

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

art297 — Wed, 10 May 2017 17:06:59 GMT

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

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

PaigeMiller — Wed, 10 May 2017 17:09:47 GMT

@ImPrem wrote:

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.

If you heard that SAS was very simple, then I suggest that this is incorrect. SAS can get complicated when doing complicated tasks. I doubt you will find simpler code in a data step, otherwise Art would have provided it.

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

ballardw — Wed, 10 May 2017 17:12:49 GMT

Exactly how you want the results can have a big difference on an approach. If I just wanted to know the value with out the result in a data set I would start with:

Proc univariate data=employeesalary;

var salary.

run;

One of the results will be a list of the 5 largest and 5 smallest values of the variable.

Or just largest and smallest:

Proc means data=employeesalary min max;

var salary;

run;

Means has some options to get top and bottom 3 as well.

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

ImPrem — Wed, 10 May 2017 17:42:43 GMT

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!

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

Reeza — Wed, 10 May 2017 17:44:29 GMT

@ImPrem You're asking us to do your assignment rather than taking the time to learn. And your instructor will know because you're expected to use the techniques taught in class and the solutions we provide may not align with what you were taught and the type of solution you should be implementing. If you want to learn SAS invest the time to learn, if you're just trying to pass a course for some reason then good luck.

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

ImPrem — Wed, 10 May 2017 17:55:33 GMT

Thank you for your kind friendly advise!! Today @art297 taught me the new technique to do in different way! I'm amazed! Looking forward to do the same task in a different way. So, i will also learn from expert like you people. @Reeza Kindly guide me with some study materials for begineers like me.

Once again thank you very much!!

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

Reeza — Wed, 10 May 2017 18:16:27 GMT

Support.sas.com/training/tutorial

http://stats.idre.ucla.edu/sas/modules/

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

Ksharp — Thu, 11 May 2017 13:00:51 GMT

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

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

ImPrem — Thu, 11 May 2017 14:21:55 GMT

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

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

thisisneeraj — Thu, 11 May 2017 14:26:09 GMT

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.

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

ImPrem — Thu, 11 May 2017 14:32:48 GMT

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!!!! 🙂

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

thisisneeraj — Thu, 11 May 2017 14:46:12 GMT

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.

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

art297 — Thu, 11 May 2017 15:32:17 GMT

@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

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

Ksharp — Fri, 12 May 2017 12:28:57 GMT

@art297 ,

I don't think you are contender for me. I think you are partner for me . We both have the same papers in SGF.

Most time I agree with your solution, and of course learn a lot from you too.

I would like to say welcome back sas forum.

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

thisisneeraj — Fri, 12 May 2017 15:37:12 GMT

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 ...!!