data have;
input id cause effect tally;
datalines;
1 91 80 30
2 23 85 28
3 34 86 95
4 15 45 25
5 28 87 24
6 10 90 28
7 23 95 28
;
run;
Hi Community,
I have a requirement to get the value closest to tally and a new variable named COMP must be created. For ex. for ID = 1 then 30 should be compared to 91 and 80 and as 80 is closest 30 - COMP = 80.
id cause effect tally COMP 1 91 80 30 80 2 23 85 28 23 3 34 86 95 86 4 15 45 25 15 5 28 87 24 28 6 10 90 28 10 7 23 95 28 23
Any ideas??
I think this does what you are looking for.
data want; set have; array c (*) cause effect; comp =c [ whichn(min(abs(tally-cause),abs(tally-effect)),abs( tally-cause),abs( tally-effect) ) ]; run;
The WHICHN function compares the first value, in this case the minimum absolute difference between the two values, and returns the position of the value in the other other parameters or a zero. An array provides a way to use that ordered value to select the original value you want.
Caution: if you have missing values for Tally this doesn't work. If both cause and effect are missing this also will not work and will cause "ERROR: Array subscript out of range" and not execute. If you have missing values that may do that you need to test and not execute the Comp= line.
So you have two values, cause and effect and you want to see which is closes to the tally variable within the same row?
Take the difference and then use the smaller one with IF statements seems like the simplest solution (untested).
data want;
set have;
dif_tally_cause = tally - cause;
dif_tally_effect = tally -effect;
*verify the logic here, didn't spend any time testing;
if diff_tally_cause > diff_tally_effect then comp = effect;
else comp = tally;
run;
If you want to factor in the direction of the difference use the ABS() function in the IF statement.
if abs(diff_tally_cause) > abs(diff_tally_effect) then comp = effect;
else comp = tally;
@shasank wrote:
data have; input id cause effect tally; datalines; 1 91 80 30 2 23 85 28 3 34 86 95 4 15 45 25 5 28 87 24 6 10 90 28 7 23 95 28 ; run;
Hi Community,
I have a requirement to get the value closest to tally and a new variable named COMP must be created. For ex. for ID = 1 then 30 should be compared to 91 and 80 and as 80 is closest 30 - COMP = 80.
id cause effect tally COMP
1 91 80 30 80 2 23 85 28 23 3 34 86 95 86 4 15 45 25 15 5 28 87 24 28 6 10 90 28 10 7 23 95 28 23
Any ideas??
I think this does what you are looking for.
data want; set have; array c (*) cause effect; comp =c [ whichn(min(abs(tally-cause),abs(tally-effect)),abs( tally-cause),abs( tally-effect) ) ]; run;
The WHICHN function compares the first value, in this case the minimum absolute difference between the two values, and returns the position of the value in the other other parameters or a zero. An array provides a way to use that ordered value to select the original value you want.
Caution: if you have missing values for Tally this doesn't work. If both cause and effect are missing this also will not work and will cause "ERROR: Array subscript out of range" and not execute. If you have missing values that may do that you need to test and not execute the Comp= line.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.