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.
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;
data want;
set have;
array arr
length WANT_Lowest WANT_Highest $32;
WANT_Lowest = vname(arr[whichn(min(of arr
WANT_Highest = vname(arr[whichn(max(of arr
run;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.