topic Re: How to find the second highest/ lowest values across the column in New SAS User

How to find the second highest/ lowest values across the column

awais — Thu, 10 Oct 2019 03:45:32 GMT

Hello SAS Community,

I am wondering if there is any function, similar to Min and Max function, to identify the second or third highest or lowest value across the column.

example: We are given student_Ids with average monthly results. We have to calculate highest, second highest, third highest avg. monthly score. Something similar to the following dataset:

Student_ID	Name	Class	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Max	Min	Second_Highest	Third_highest
A-1101	John	8	34	33	28	27	27	28	38	50	42	44	24	20
A-1102	Peter	7	36	35	30	29	29	30	40	52	44	46	26	22
A-1103	Aslam	8	38	37	32	31	31	32	42	54	46	48	28	24
A-1104	Neeraj	9	40	39	34	33	33	34	44	56	48	50	30	26
A-1105	Kulsoom	7	42	41	36	35	35	36	46	58	50	52	32	28

Re: How to find the second highest/ lowest values across the column

Patrick — Thu, 10 Oct 2019 04:05:25 GMT

SAS functions LARGEST() and SMALLEST() can do such things.

data have;
  infile datalines truncover dlm='|';
  input (Student_ID	Name) ($)	Class	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	;
  array months {*} Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec;
  Max=max(of months[*]);
  Min=min(of months[*]);
  Second_Highest=largest(2, of months[*]);
datalines;
A-1101|John|8|34|33|28|27|27|28|38|50|42|44|24|20| | | | 
A-1102|Peter|7|36|35|30|29|29|30|40|52|44|46|26|22| | | | 
A-1103|Aslam|8|38|37|32|31|31|32|42|54|46|48|28|24| | | | 
A-1104|Neeraj|9|40|39|34|33|33|34|44|56|48|50|30|26| | | | 
A-1105|Kulsoom|7|42|41|36|35|35|36|46|58|50|52|32|28| 
;
proc print;
run;

Re: How to find the second highest/ lowest values across the column

Kurt_Bremser — Thu, 10 Oct 2019 06:34:21 GMT

@Patrick wrote:

SAS functions LARGEST() and SMALLEST() can do such things.

Maxim 9 in action. Learned something new today (Maxim 13).

Re: How to find the second highest/ lowest values across the column

awais — Fri, 11 Oct 2019 01:31:47 GMT

Hi Patrick,

Ditto! Excellent. Never knew that :). thank you for your help!

awais