Creating variable with value of the absolute difference between ''x'' of a certain ''y'' and ''x'' of the closest ''y'' value to this ''y''

[Joe Cullen]

Hi all,

I'm currently writing my master's thesis and i'm a little bit stuck at the moment.
This is a simplified view of my sample:

DA roa
0.1 1.2
0.5 1.5
0.7 1.6
0.2 1.9
0.1 2.8
0.6 3.6
0.4 2.0
0.2 0.5

I would like to create a new variable that is based on the absolute difference between DA of a value of a certain roa, and DA of the value of roa that is closest to the value of this roa.


So for example, the closest value of the first roa (1.2) is 1.5. So I would like to create a new variable with the value (0.5-0.1)= 0.4 for the first line, and (0.7-0.5)= 0.2 for the second line, because 1.5 is closest to 1.6.

Is there someone who can help me out?

Thank you in advance!

[Andrew Musau]

With duplicate values of "roa", you need to store the variable in a separate dataset, contract it, generate closest match and merge back assuming that you are interested in the closest distinct value. Otherwise, here is one way to get what you want.

input float(DA roa)
0.1 1.2
0.5 1.5
0.7 1.6
0.2 1.9
0.1 2.8
0.6 3.6
0.4 2.0
0.2 0.5
end

gen order=_n
sort roa
gen closest= cond(abs(roa-roa[_n+1]) < abs(roa-roa[_n-1]),roa[_n+1],  roa[_n-1] )
gen closestDA= cond(closest==roa[_n+1], DA[_n+1], DA[_n-1])
gen wanted= closestDA- DA
sort order
l, sep(10)

Res.:

.
. l, sep(10)

     +------------------------------------------------+
     | DA   roa   order   closest   closes~A   wanted |
     |------------------------------------------------|
  1. | .1   1.2       1       1.5         .5       .4 |
  2. | .5   1.5       2       1.6         .7       .2 |
  3. | .7   1.6       3       1.5         .5      -.2 |
  4. | .2   1.9       4         2         .4       .2 |
  5. | .1   2.8       5         2         .4       .3 |
  6. | .6   3.6       6       2.8         .1      -.5 |
  7. | .4     2       7       1.9         .2      -.2 |
  8. | .2    .5       8       1.2         .1      -.1 |
     +------------------------------------------------+

.
​​​​​​

[Nick Cox]

Andrew Musau's very helpful code jumps one way if two values are equally close, a case not discussed in #1. You need to check what it does.

[Joe Cullen]

Andrew Musau Thank you so much! This is exactly what I wanted the output to be.

Nick Cox The roa in my sample has at least 8 decimals so equally close barely occurs in my case. Thank you for your remark!