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
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;
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;
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.
Hi
I solved it. Thanks for the help.
The second table also has ID? Generally for med tables they don't.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.