Desktop productivity for business analysts and programmers

Join using a partial match

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Join using a partial match

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


Accepted Solutions
Solution
‎10-04-2014 04:49 PM
Trusted Advisor
Posts: 1,203

Re: Join using a partial match

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;

View solution in original post


All Replies
Solution
‎10-04-2014 04:49 PM
Trusted Advisor
Posts: 1,203

Re: Join using a partial match

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;

New Contributor
Posts: 2

Re: Join using a partial match

Wonderful, thank you!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 363 views
  • 0 likes
  • 2 in conversation