How to create column with name of column with the highest value per each ID in SAS Enterprise Guide / PROC SQL?

Question

I have table in SAS Enterprise Guide like below:

ID   | COL_A | COL_B | COL_C
-----|-------|-------|------
111  | 10    | 20    | 30
222  | 15    | 80    | 10
333  | 11    | 10    | 20
444  | 20    | 5    | 20

Requirements:

• And I need to create new column "TOP" where will be the name of column with the highest values for each ID.

• If for example 2 or more columns have the same highest value take the first under the alphabet.

Desire output:

ID   | COL_A | COL_B | COL_C  | TOP
-----|-------|-------|--------|-------
111  | 10    | 20    | 30     | COL_C
222  | 15    | 80    | 10     | COL_B
333  | 11    | 10    | 20     | COL_C
444  | 20    | 5     | 20     | COL_A

Becasue:

• for ID = 111 the highest value is in COL_C, so name "COL_C" is in column "TOP"
• for ID = 444 two columns have the highest value, so based on alpabet criterion in column "TOP" is name "COL_A"

How can i do that in SAS Enterprise Guide or in PROC SQL ?

Answer 1

This you can do with functions. Use MAX() to find the largest value. Use WHICHN() to find the index number of the first variable with that value. Use the VNAME() function to get the name of the variable with that index.

data want;
  set have;
  length TOP $32;
  array list col_a col_b col_c;
  top = vname(list[whichn(max(of list[*]),of list[*])]);
run;