topic Re: Join using a partial match in SAS Enterprise Guide

Join using a partial match

_robindavies — Sat, 04 Oct 2014 20:00:05 GMT

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

Re: Join using a partial match

stat_sas — Sat, 04 Oct 2014 20:49:11 GMT

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;

Re: Join using a partial match

_robindavies — Sat, 04 Oct 2014 21:19:10 GMT

Wonderful, thank you!!