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!!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.