topic Re: For each observation, find the variable that contains the minimum value in SAS Procedures

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

buckeyefisher — Wed, 16 Jul 2014 15:27:51 GMT

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?

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

Reeza — Wed, 16 Jul 2014 15:47:16 GMT

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;

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

stat_sas — Wed, 16 Jul 2014 15:51:20 GMT

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;

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

vinayemmadi — Wed, 16 Jul 2014 17:51:33 GMT

use arrays and do loop to check min value

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

buckeyefisher — Wed, 16 Jul 2014 17:56:06 GMT

Reeza,

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

Thanks

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

jmdecu — Fri, 18 Jul 2014 16:37:19 GMT

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!

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

buckeyefisher — Fri, 18 Jul 2014 16:48:47 GMT

jmdecu,

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

thanks

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

Reeza — Fri, 18 Jul 2014 17:24:39 GMT

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