topic Re: Matching Data when they are not exactly same in SAS Programming

Matching Data when they are not exactly same

Yvegunta — Thu, 14 Jul 2016 07:47:11 GMT

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.

Name1	Name2
Valentino Rossi
Marc Marquez	Marc
George Lorenzo
Valentino	Valentino

The Output should look something like below:

Combined Data
Name1	Name2
Valentino Rossi	Valentino
Marc Marquez	Marc
George Lorenzo
Valentino	Valentino

Appritiate your help.

Thanks

Re: Matching Data when they are not exactly same

udden2903 — Thu, 14 Jul 2016 08:26:13 GMT

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;

Re: Matching Data when they are not exactly same

Ksharp — Thu, 14 Jul 2016 08:32:28 GMT

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;

Re: Matching Data when they are not exactly same

ctrlx — Thu, 14 Jul 2016 12:54:49 GMT

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;

Re: Matching Data when they are not exactly same

Yvegunta — Fri, 15 Jul 2016 03:54:10 GMT

Thanks udden2903 .

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

Great Help. Thank you

Re: Matching Data when they are not exactly same

Yvegunta — Fri, 15 Jul 2016 03:55:55 GMT

Thank you Ksharp

Re: Matching Data when they are not exactly same

Yvegunta — Fri, 15 Jul 2016 03:56:27 GMT

Thank you Ctrlx