Method to select the max across columns and retrieving the field name

[evaaseow]

Hi All,

I have a problem, I need to select the max value of a row and get the field(s) that its associated to.

Example:
ID MONTH1 MONTH2 MONTH3 MONTH4 MONTH5 MONTH6
1 22 33 32 56 234 234

I need to be able to select and retrieve ID, MONTH5 and MONTH6 (not the values but the column name) as my result set.

I know you can do a max(column1,column2,column3) and it returns the correct value but have no idea on how to get the column name.

Any help would be amazing.

 

[ChrisW75]

Probably the easiest way to do this is to use an array.
Run this (it's self contained) and see if it does what you want it to.

data test;
  ID = 1;
  month1=22;
  month2=33;
  month3=32;
  month4=56;
  month5=234;
  month6=234;
run;

data test2(drop=i j);
  set test;
  array months{6}  month1-month6;
  array tops{6}  $  top1-top6;

  * Get largest value *;
  max=max(of month1-month6);

  j = 1;
  * Build list of variables which contain this value *;
  do i = 1 to 6;
    if months{i} = max then
    do;
      tops{j} = catt("month",left(put(i,2.)));
      j + 1;
    end;
  end;

run;

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.