Join using a partial match

[_robindavies]

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

[stat_sas · Accepted solution]

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;

[_robindavies]

Wonderful, thank you!!