BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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        

 

 

 

 

5 REPLIES 5
PGStats
Opal | Level 21

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
Ksharp
Super User

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
Barite | Level 11

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
Barite | Level 11

Hi

 

I solved it. Thanks for the help.

 

 

Reeza
Super User

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 940 views
  • 3 likes
  • 4 in conversation