DATA Step, Macro, Functions and more

how to concatenate two variable values and display in one variable

Reply
Super Contributor
Posts: 272

how to concatenate two variable values and display in one variable

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        

 

 

 

 

Respected Advisor
Posts: 4,651

Re: how to concatenate two variable values and display in one variable

[ Edited ]

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;

PG
Super User
Posts: 9,682

Re: how to concatenate two variable values and display in one variable


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;

Super Contributor
Posts: 272

Re: how to concatenate two variable values and display in one variable

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.

Super Contributor
Posts: 272

Re: how to concatenate two variable values and display in one variable

Hi

 

I solved it. Thanks for the help.

 

 

Super User
Posts: 17,864

Re: how to concatenate two variable values and display in one variable

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

Ask a Question
Discussion stats
  • 5 replies
  • 208 views
  • 3 likes
  • 4 in conversation