For each observation, find the variable that contains the minimum value

[buckeyefisher]

For each observation, I want to find the variable that contains the maximum value

here is the File structure

i/p

Obs        CompanyID              Year       Col1       Col2       col3       col4       col5

1             AAAA                    1991           2             3             6             7             2

2             BBBB                    1992           6             9             3             2             0

Intended o/p

Obs        CompanyID         Year       Col1       Col2       col3       col4       col5                Max

1             AAAA                    1991      2             3             6             7             2             col4

2             BBBB                    1992      6             9             3             2             0             col2

I used the following code but it just creates column names in the o/p file.

proc iml;

use FileA;

read all var _NUM_ INTO X [colname=VarNames];

close FileA;

idxMax = X[,<:>]; /* find columns for max of each row */

varMax = varNames[idxMax]; /* corresponding var names */

print idxMax varNames;

run;

Any thoughts? Is there a simple way in SQL?

[Reeza · Accepted solution]

Using a data step works easily enough, not sure why you went into IML, unless you really need to, but then SQL wouldn't be an option either.

data want;

set have;

array col(5) col1-col5;

max_value=max(of col(*));

var_max=vname(col(whichn(max_value, of col(*))));

run;

[jmdecu]

Hi Reeza,

How does this solution handle ties?  In other words, if two variables contain the maximum value for the row, will the names of both variables be flagged?  Thanks!

[Reeza]

It doesn't.  If you want to handle ties you should probably use a SQL approach.

[stat_sas]

If you need sql then you can try this.

proc transpose data=have(keep=obs col:) out=want;
by obs;
run;

proc sql;
select a.*,b._name_ from have a
inner join (
select obs, _name_  from want
group by obs
having col1=max(col1)) b
on a.obs=b.obs;
quit;

[vinayemmadi]

use arrays and do loop to check min value

[buckeyefisher]

Reeza,

simple SAS logic worked. thanks. stat@sas  - i didn't try SQL logic but will keep it for future reference.

Thanks

[buckeyefisher]

jmdecu,

good catch..I am also exploring of a fix...

thanks