topic Conditional Merge of 2 Datasets by derived variable in New SAS User

Conditional Merge of 2 Datasets by derived variable

efi88 — Wed, 20 Feb 2019 20:54:21 GMT

Hi Guys,

I need to merge two datasets by a variable that is included in other variable in the second dataset.

for example:

dataset#1

names	data
ABLIM1 ABLIM KIAA0059 LIMAB1	data1
AK3 AK3L1 AK6 AKL3L	data2
AK4 AK3 AK3L1	data3
ADD3 ADDL	data4
PHYKPL AGXT2L2 PP9286	data5

dataset#2

name	more data
ABLIM	more data 1
ADDL	more data 2
DKG	more data 3
PRWR	more data 4

The result I need:

name	names	data	more data
ABLIM	ABLIM1 ABLIM KIAA0059 LIMAB1	data1	more data 1
ADDL	ADD3 ADDL	data4	more data 2
DKG			more data 3
PRWR			more data 4

Thank you!

Re: Conditional Merge of 2 Datasets by derived variable

novinosrin — Wed, 20 Feb 2019 21:07:53 GMT

data one;
input names & $50.	data $20.;
cards;
ABLIM1 ABLIM KIAA0059 LIMAB1	data1 
AK3 AK3L1 AK6 AKL3L	  data2
AK4 AK3 AK3L1	 data3
ADD3 ADDL	  data4
PHYKPL AGXT2L2 PP9286	 data5
;

data two;
input name $	more_data & $15.;
cards;
ABLIM	more data 1
ADDL	more data 2
DKG	more data 3
PRWR	more data 4
;

proc sql;
create table want as
select a.*,b.names,data
from two a left join one b
on b.names contains strip(a.name);
quit;

Re: Conditional Merge of 2 Datasets by derived variable

efi88 — Wed, 20 Feb 2019 21:43:54 GMT

Thank you so much!