sasaggregationproc-sqlenterprise-guide4gl

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


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:

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:

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


Solution

  • 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;