topic Re: Find second largest value in array and return column name in SAS Programming

Find second largest value in array and return column name

ConsAth — Fri, 16 Sep 2016 15:00:43 GMT

Hi all,

What i am trying to accomplish is the following , i have an array with 4 variables containing values lest say 1, 2 ,5, 6 etc.

I am able to get the max value of the array and have the column name returned but I am stuck on how I can do the same procedure for the next largest value, any ideas?

Kind regards and thank you in advance

This is my script up till now

DATA WANT;
SET HAVE;

ARRAY COL(*) col1 --col4;

INDEX_OF_MAX=WHICHN(MAX(OF COL(*)),OF COL(*));
VALUE_1=VNAME(COL(INDEX_OF_MAX));
RUN;

Re: Find second largest value in array and return column name

data_null__ — Fri, 16 Sep 2016 15:27:42 GMT

Use function LARGEST

Re: Find second largest value in array and return column name

ConsAth — Fri, 16 Sep 2016 15:37:36 GMT

Thank you for your input but it doesnt cover my query on how to get the column name of that second largest value, thanks again

Re: Find second largest value in array and return column name

data_null__ — Fri, 16 Sep 2016 16:08:11 GMT

You know how to do that as demonstrated in the code you posted. You just need to know how to find the second largest value.

Re: Find second largest value in array and return column name

ConsAth — Fri, 16 Sep 2016 16:11:56 GMT

Yes, but i have been stuck in using the same method for second max value, i know i could use largest function but that would give me only which are the largest values not the column name they come from , for example here in this part of script INDEX_OF_MAX=WHICHN(MAX(OF COL(*)),OF COL(*)); i am trying to do the same for lets say MAX -1 and then use the Vname to get it, dont know have been workind around on this too much time and maybe i am missing the obvious

Re: Find second largest value in array and return column name

data_null__ — Fri, 16 Sep 2016 16:20:06 GMT

data want;
   array col(5) (1 3 5 10 4);
   index_of_2nd=whichn(largest(2,of col(*)),of col(*));
   value_1=vname(col(index_of_2nd));
   run;

Re: Find second largest value in array and return column name

ConsAth — Fri, 16 Sep 2016 16:40:27 GMT

Have to say you opened my eyes, i was thinking whichn and largest functions separetely and not combined, thank you very much