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 |
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.