how to concatenate two variable values and display in one variable

[knveraraju91]

Hi,

I have to merge two data sets. For second OBS, I have to concatenate two values from data2 because data1 has aen1 and aen2 values are populated. How to merge the values . Please help. Thanks

data1

ID         trt                                 aen1             aen2

1          paracetamol                   1

1          ibuporfen                         1                    2

data2

id         term                aen

1           headche          1

1          cramps              2

output needed;

id         trt                                        indication

1          paracetamol                       headche

1           ibuporfen                          headche,cramps        

[PGStats]

If there are only 2 aen columns, this can be practical:

proc sql;
create table data3 as
select a.id, a.trt, catx(",", b.term, c.term) as indication length=25
from data1 as a left join
    data2 as b on a.id=b.id and a.aen1=b.aen left join
    data2 as c on a.id=c.id and a.aen2=c.aen;
select * from data3;
quit;

[Ksharp]

data data1;
input ID         trt    : $20.    aen1             aen2;
cards;
1          paracetamol                   1   .
1          ibuporfen                         1                    2
;
run;
 
 
data data2; 
input id         term    $            aen;
cards;
1           headche          1
1          cramps              2
;
run;

data want;
 if _n_=1 then do;
  if 0 then set data2;
  declare hash h(dataset:'data2');
  h.definekey('id','aen');
  h.definedata('term');
  h.definedone();
 end;
set data1;
array x{*} aen1-aen2;
length indication $ 200;
do i=1 to dim(x);
 aen=x{i};
 if h.find()=0 then indication=catx(',',indication,term);
end;
drop i aen term;
run;

[knveraraju91]

Hi

Thanks for the code. It worked. But I need to have a space after comma.

The output I am getting "headche,cramps". 

Output I need is  "headche, cramps".  I tried different ways but could not solve. Please help. Thanks.

[knveraraju91]

Hi

I solved it. Thanks for the help.

[Reeza]

The second table also has ID? Generally for med tables they don't.