DATA Step, Macro, Functions and more

Help with Table Lookup/Merge

Reply
Occasional Learner
Posts: 1

Help with Table Lookup/Merge

[ Edited ]

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

Var1var2var3
John Doeoneyes
John Daotwono
John Laouoneyes
Laou Johntwoyes
Ram Prasad Neulaoneyes
Ram Neulatwono
Prasad Ram Neulaoneyes
Neula Ram Prasadtwoes
Ram Prasad Doeoneno
California Mayor Cityoneyes
California City Mayor Towntwoes
California Mayoroneno
California City Mayor Houseoneyes

 

Dataset 2

var1var4var5
Johnsomething1something2
Ram Prasadsomething3something4
california city mayorhelohelo2

 

Desired Dataset

Var1var2var3var4var5
John Doeoneyessomething1something2
John Daotwonosomething1something2
John Laouoneyessomething1something2
Laou Johntwoyes  
Ram Prasad NeulaoneyesSomething3something4
Ram Neulatwono  
Prasad Ram Neulaoneyes  
Neula Ram Prasadtwoes  
Ram Prasad DoeonenoSomething3something4
California Mayor Cityoneyes  
California City Mayor Towntwoeshelohelo2
California Mayoroneno  
California City Mayor Houseoneyeshelohelo2
PROC Star
Posts: 7,360

Re: Help with Table Lookup/Merge

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

 

Super User
Posts: 9,671

Re: Help with Table Lookup/Merge


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;

Respected Advisor
Posts: 4,642

Re: Help with Table Lookup/Merge

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;
PG
Ask a Question
Discussion stats
  • 3 replies
  • 128 views
  • 0 likes
  • 4 in conversation