topic Re: How to get value closest to another variable from a series in SAS Programming

How to get value closest to another variable from a series

shasank — Fri, 19 Feb 2021 17:33:17 GMT

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??

Re: How to get value closest to another variable from a series

Reeza — Fri, 19 Feb 2021 17:37:14 GMT

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??

Re: How to get value closest to another variable from a series

shasank — Fri, 19 Feb 2021 17:40:00 GMT

Thank you Reeza. Correct. Cause and Effect should be compared to tally and get the closest. But, I have 7 variable that should be compared to tally. So, was asking to see if there is an advanced way.

Re: How to get value closest to another variable from a series

ballardw — Fri, 19 Feb 2021 17:47:54 GMT

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.