Adding a variable from another table based on a condition

[AmirSari]

Hi everyone,

I want to add column Y from table B to table A if, for each ID, the values in column Y are right after or before the values of column X in table A.

table A:

ID    X

1     9

2     6

3   11

table B:

ID    Y

1     4

1     3

1     7

1     11

1     16

2     1

2     8

2     5

2     9

3   7

3   9

3   14

3   13

What I need:

ID    X     Y

1     9     7

1     9    11

2     6     5

2     6     8

3   11     9

3   11     13

Thank you!

[Tom]

You could probably get away with interleaving the two datasets.

First let's convert your example listings into actual datasets.

data a;
  input id x;
cards;
1  9
2  6
3 11
;
data b;
  input id y ;
cards;
1 4
1 3
1 7
1 11
1 16
2 1
2 8
2 5
2 9
3 7
3 9
3 14
3 13
;

For the interleaving to work they need to be sorted.

proc sort data=a;
  by id x;
run;
proc sort data=b;
  by id y;
run;

Now since you want to interleave by both the ID and the other variable you will have rename on on the way in.

To find the previous value use LAG().

data want;
  set b(in=inb rename=(y=x)) a(in=ina);
  by id x;
  lagx=lag(x);
  if first.id then lagx=.;
  if ina then y=lagx;
  if inb and not first.id and lag(ina)=1 then do; y=x; x=lagx; end;
  if not missing(y);
  drop lagx;
run;

[AmirSari]

Thank you! This also worked!

[andreas_lds · Accepted solution]

Just an idea:

data distance;
   merge two one;
   by Id;
   
   d = x - y;
   s = sign(d);
   d = abs(d);
run;

proc sort data=distance out=sorted;
   by id s d;
run;

data want;
   set sorted;
   by id s;
   
   if first.s;
   
   drop s d;
run;