topic Re: Adding a variable from another table based on a condition in SAS Programming

Adding a variable from another table based on a condition

AmirSari — Thu, 19 Jan 2023 01:46:02 GMT

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!

Re: Adding a variable from another table based on a condition

Tom — Thu, 19 Jan 2023 04:43:08 GMT

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;

Re: Adding a variable from another table based on a condition

andreas_lds — Thu, 19 Jan 2023 06:29:16 GMT

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;

Re: Adding a variable from another table based on a condition

AmirSari — Thu, 19 Jan 2023 08:09:22 GMT

Thank you! This also worked!