topic Idenfitying variable with the lowest/highest values in SAS Procedures

Idenfitying variable with the lowest/highest values

Tpham — Thu, 18 Dec 2014 14:26:17 GMT

I have this sample dataset and want to identify the variable with either the highest or lowest value. So in this case not to report the value but report the variable. Does anyone have any suggestions on how to do this?

ID	Var1	Var2	Var3	WANT_Lowest	WANT_Highest
1	20	50	30	Var1	Var2
2	0.90	0.25	0.10	Var3	Var1

So I want these two variables. As we can see with ID=1, the highest value is Var2 so the WANT_highest is coded Var2. Similaringly with the lowest for Var1.

What is the easiest way to do this? I know the max/min function will return the values. But in this case I want it to return the variable name.

Re: Idenfitying variable with the lowest/highest values

RW9 — Thu, 18 Dec 2014 14:47:20 GMT

Hi,

Sorry, let me re-do that, I see you don't want the value. I would transpose, then you can do normal min/max:

data have;

id=1; var1=20; var2=50; var3=30; output;

run;

proc transpose data=have out=want;

by id;

var var1-var3;

run;

Arrays are probably the easiest:
data want;

set have;

array var{3};

want_lowest=min(of var{*});

want_highest=max(of var{*});

run;

Re: Idenfitying variable with the lowest/highest values

art297 — Thu, 18 Dec 2014 14:48:15 GMT

data want;

set have;

array arr

Var1--Var3;

length WANT_Lowest WANT_Highest $32;

WANT_Lowest = vname(arr[whichn(min(of arr

), of arr

)]);

WANT_Highest = vname(arr[whichn(max(of arr

), of arr

)]);

run;

Re: Idenfitying variable with the lowest/highest values

Loko — Thu, 18 Dec 2014 15:08:32 GMT

hello,

data have;
input ID Var1 Var2 Var3 ;
datalines;
1 20 50 30
2 0.90 0.25 0.10
;
run;

data want;
set have;
array vars{*} var1-var3;
max_val=max(var1, var2,var3);
min_val=min(var1, var2,var3);

do i=1 to dim(vars) until(vars{i}=max_val);
WANT_Highest=vname(vars{i});
end;

do i=1 to dim(vars) until(vars{i}=min_val);
WANT_Lowest=vname(vars{i});
end;

drop max_val min_val i;
run;