Help with Table Lookup/Merge

[amulyabista]

Hi,

I am trying to merge two dataset by var1 but with a lookup method rather than exact match merge. Please help

Data Set 1

Var1	var2	var3
John Doe	one	yes
John Dao	two	no
John Laou	one	yes
Laou John	two	yes
Ram Prasad Neula	one	yes
Ram Neula	two	no
Prasad Ram Neula	one	yes
Neula Ram Prasad	two	es
Ram Prasad Doe	one	no
California Mayor City	one	yes
California City Mayor Town	two	es
California Mayor	one	no
California City Mayor House	one	yes

Dataset 2

var1	var4	var5
John	something1	something2
Ram Prasad	something3	something4
california city mayor	helo	helo2

Desired Dataset

Var1	var2	var3	var4	var5
John Doe	one	yes	something1	something2
John Dao	two	no	something1	something2
John Laou	one	yes	something1	something2
Laou John	two	yes
Ram Prasad Neula	one	yes	Something3	something4
Ram Neula	two	no
Prasad Ram Neula	one	yes
Neula Ram Prasad	two	es
Ram Prasad Doe	one	no	Something3	something4
California Mayor City	one	yes
California City Mayor Town	two	es	helo	helo2
California Mayor	one	no
California City Mayor House	one	yes	helo	helo2

[art297]

You could use something like:

proc sql noprint;
  create table want as
    select *
      from dataset1 a
        left join dataset2 b
          on  upcase(a.var1) like catt(upcase(b.var1),'%')
  ;
quit;

Art, CEO, AnalystFinder.com

[Ksharp]

data DataSet1;
infile cards dlm='09'x;
input (Var1	var2	var3) (:$40.);
cards;
John Doe	one	yes
John Dao	two	no
John Laou	one	yes
Laou John	two	yes
Ram Prasad Neula	one	yes
Ram Neula	two	no
Prasad Ram Neula	one	yes
Neula Ram Prasad	two	es
Ram Prasad Doe	one	no
California Mayor City	one	yes
California City Mayor Town	two	es
California Mayor	one	no
California City Mayor House	one	yes
;
run;
data Dataset2;
infile cards dlm='09'x;
input (var1	var4	var5) (:$40.);
cards;
John	something1	something2
Ram Prasad	something3	something4
california city mayor	helo	helo2
;
run;
proc sql ;
  create table want as
    select a.*,var4,var5
      from dataset1 a
        left join dataset2 b
          on  prxmatch(cats('/\b',b.var1,'\b/i'),a.var1);
  ;
quit;

[PGStats]

Or

proc sql;
create table want as
select 
	a.*,
	b.var4,
	b.var5
from 
	dataset1 as a left join 
	dataset2 as b on upcase(a.var1) eqt upcase(b.var1);
quit;