Matching Data when they are not exactly same

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Matching Data when they are not exactly same

Hi All,

 

I have 2 datasets, which i need to match and delete those records which match. Below is sample of what i am referring to:

Dataset1:

Name1
Valentino Rossi
Marc Marquez
George Lorenzo
Valentino

 

Dataset2:

Name2
Valentino
Marc

 

i am using query below:

 

proc sql;

CREATE TABLE del_name as Select

A.*,

B.*

 

FROM Work.dateset1 as a

left join Work.Dateset2 as B

on A.Name like B.Name

;

run;

quit;

 

The Issue is that, it matches only exact cases and does not consider anything preceeding the first name.

Name1Name2
Valentino Rossi 
Marc MarquezMarc
George Lorenzo 
ValentinoValentino

 

The Output should look something like below:

 

Combined Data 
Name1Name2
Valentino RossiValentino
Marc MarquezMarc
George Lorenzo 
ValentinoValentino

 

Appritiate your help.

 

Thanks


Accepted Solutions
Solution
‎07-14-2016 11:54 PM
Contributor
Posts: 28

Re: Matching Data when they are not exactly same

data a;
	infile datalines delimiter=',';
	length name $30;
	input name;
	datalines;
Valentino Rossi
Marc Marquez
George Lorenzo
Valentino
;

data b;
	infile datalines delimiter=',';
	length name $30;
	input name;
	datalines;
Valentino
Marc
;



proc sql;
	create table want as
		select a.name as name1, 
			b.name as name2
		from a
			left join b on a.name like cats('%', b.name, '%');
quit;

View solution in original post


All Replies
Solution
‎07-14-2016 11:54 PM
Contributor
Posts: 28

Re: Matching Data when they are not exactly same

data a;
	infile datalines delimiter=',';
	length name $30;
	input name;
	datalines;
Valentino Rossi
Marc Marquez
George Lorenzo
Valentino
;

data b;
	infile datalines delimiter=',';
	length name $30;
	input name;
	datalines;
Valentino
Marc
;



proc sql;
	create table want as
		select a.name as name1, 
			b.name as name2
		from a
			left join b on a.name like cats('%', b.name, '%');
quit;
Occasional Contributor
Posts: 13

Re: Matching Data when they are not exactly same

Thanks udden2903 .

That was a bulls eye. I was unsure on how i could use that (cats('%', b.name, '%')Smiley Wink.

 

Great Help. Thank you 

Grand Advisor
Posts: 9,596

Re: Matching Data when they are not exactly same

If you want match the words start with in each other .

 

data Dataset1;
input Name $40.;
cards;
Valentino Rossi
Marc Marquez
George Lorenzo
Valentino
;
run;
 
data Dataset2;
input Name $40.;
cards;
Valentino
Marc
;
run;

proc sql;
CREATE TABLE del_name as Select
A.name as name1,
B.name as name2
 
FROM Work.dataset1 as a
left join Work.Dataset2 as B
on A.Name eqt strip(B.Name)
;
quit;

 

Occasional Contributor
Posts: 13

Re: Matching Data when they are not exactly same

Thank you Ksharp

New Contributor
Posts: 3

Re: Matching Data when they are not exactly same

[ Edited ]
data names;
length name $20;
input name;
datalines;
Valentino Rossi
Marc Marquez
George Lorenzo
Valentino
;
run;
data nicknames;
length nickname $20;
input nickname;
datalines;
Valentino
Marc
;
run;
data test;
set names;
match=0;
do i=1 to xnobs;
set nicknames nobs=xnobs point=i;
if index(upcase(strip(name)),upcase(strip(nickname)))>0 then do; match=1; output; end;
end;
if match=0 then do;nickname=.;output;end;
drop match;
run;
proc print data=test;
run;
Occasional Contributor
Posts: 13

Re: Matching Data when they are not exactly same

Thank you Ctrlx

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 379 views
  • 4 likes
  • 4 in conversation