Join based on a concatenated value

[mshr]

Hi,

I'm trying to join 2 tables based on a concatenated field (var1) :

Thank you

Table A:

ID	var1
1	a, b, d
2	c
3	d, a

  Table B:

var1	var2
a	John
b	Sarah
c	Tralala
d	Minnie

Result: 

ID	var 3
1	John, Sarah, Minnie
2	Tralala
3	Minnie,  John

[yabwon]

This is a step by step solution:

title "Table_A";
data Table_A;
input ID	var1 $20.;
cards;
1	a, b, d
2	c
3	d, a
;
run;
proc print;
run;

title "Table_B";
data Table_B;
input code $1. data $20.; /* variables names changed */
cards;
a	John
b	Sarah
c	Tralala
d	Minnie
;
run;
proc print;
run;


/* step 1 */
data splited;
  set Table_A;

  length code $ 1;
  do i = 1 to countw(var1,", ");
    code=scan(var1,i,", ");
    output;
  end;
drop var1;
run;

/* step 2 */
proc sql;
  create table combined as
  select s.ID, b.data
  from splited as s
  left join
  Table_B as b
  on s.code=b.code
  order by s.ID
  ;
quit;

/* step 3 */
proc transpose data=combined out=transposed;
  by ID;
  var data;
run;

/* step 4 */
title "Result";
data Result;
  set transposed;
  array C[*] col:;

  length var3 $ 128;
  var3 = catx(", ", of C[*]);
  keep ID var3;
run;
proc print data=Result;
run;
title;

The join you want can't be done in one step.

Bart

[yabwon · Accepted solution]

"The join you want can't be done in one step." ?!?! Hold my beer...

data Result_in_one_step;
  if 0 then set Table_B;
  declare hash H(dataset: "Table_B");
  H.defineKey("code");
  H.defineData("data");
  H.defineDone();

  do until (EOF);
    set Table_A end=EOF;

    length code $ 1 var3 $ 128;
    call missing(var3);
    do i = 1 to countw(var1,", ");
      code=scan(var1,i,", ");
      if 0=H.find() then var3 = catx(", ", var3, data);
    end;

    output; 
  end;

  stop;
  keep ID var3;
run;

title "Result_in_one_step";
proc print data=Result_in_one_step;
run;
title;

Bart

[mshr]

your solution works great! 

many thanks