Hi, I am struggling with a particular join that I hope has a solution in SAS. I have 2 tables as follows:
Reference table 1:
CityRef:
NewYork
London
Paris
Brussels
Canberra
Raw extracted table 2:
CityRaw:
oshNewYorkd
whatevrLondon
Parisowjshg
Brssuls
I would like the following right join to be returned:
CityRaw | CityRef
oshNewYorkd | NewYork
whatevrLondon | London
Parisowjshg | Paris
Brssuls | -
So I would like to join these two tables together if the key from table 1 can be found within the key from table 2. I hope this makes sense. Perhaps proc sql is the answer? Any help would be greatly appreciated and would save me writing a complex iterative find macro in VBA! I am using EG 9.3.
Rob
data table1;
input CityRef $10.;
datalines;
NewYork
London
Paris
Brussels
Canberra
;
data table2;
input CityRaw $15.;
datalines;
oshNewYorkd
whatevrLondon
Parisowjshg
Brssuls
;
proc sql;
select catx(" | ",a.cityraw,b.cityref) as city
from table2 a left join table1 b
on strip(upcase(cityraw)) contains strip(upcase(cityref));
quit;
data table1;
input CityRef $10.;
datalines;
NewYork
London
Paris
Brussels
Canberra
;
data table2;
input CityRaw $15.;
datalines;
oshNewYorkd
whatevrLondon
Parisowjshg
Brssuls
;
proc sql;
select catx(" | ",a.cityraw,b.cityref) as city
from table2 a left join table1 b
on strip(upcase(cityraw)) contains strip(upcase(cityref));
quit;
Wonderful, thank you!!
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.