BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yvegunta
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
udden2903
Obsidian | Level 7
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

6 REPLIES 6
udden2903
Obsidian | Level 7
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;
Yvegunta
Obsidian | Level 7

Thanks udden2903 .

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

 

Great Help. Thank you 

Ksharp
Super User

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;

 

Yvegunta
Obsidian | Level 7

Thank you Ksharp

ctrlx
Fluorite | Level 6
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;
Yvegunta
Obsidian | Level 7

Thank you Ctrlx

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 6 replies
  • 1071 views
  • 4 likes
  • 4 in conversation