Proc sql Left Join where a value isnt exact?

[mcook]

Working with data similar to the following

data table1;
input Location $ Number;
datalines;
Home 5
Store 4
work 10
Gym 3
Schools 7
Moon 8
;
run;

data table2;
input Place $;
datalines;
Home 
Work
School
Moon
;
run;

I am trying to match the Place in Table 2 with the corresponding number from Table1 with a left join.  But as you can see, there is a Pluralization and Capitalization difference between the two tables.  

Running this code

proc sql noprint;
create table Joined as 
select place, number 
from Table2 Left Join Table1
on upcase(Table2.Place) = upcase(Table1.Location);
quit;

running this outputs

I would like School to be matched with Schools.  Is there a way in a left join to accomplish this?

Or disregarding all of that.  Is there an easier way to match the values of Place in Table2 with the numbers from Table1?

[ChrisNZ]

> I would like School to be matched with Schools.
What's the match criterion?

[mcook]

I'm not sure what you  mean by match criterion.  Basically I would just like pluralization to be ignored.  

[ChrisNZ]

Another way:

where      upcase(Table2.PLACE)      =      upcase(Table1.LOCATION)
   or cats(upcase(Table2.PLACE),'S') =      upcase(Table1.LOCATION)
   or      upcase(Table2.PLACE)      = cats(upcase(Table1.LOCATION),'S')

Note that while stricter and closer to your requirements than the two solutions already proposed, this logic can still perform erroneous matches, such as BAS and BASS.

[Ksharp]

You could try some fuzz matched FUNCTION. like spedis() complev() compgen() .........

data table1;
input Location $ Number;
datalines;
Home 5
Store 4
work 10
Gym 3
Schools 7
Moon 8
;
run;

data table2;
input Place $;
datalines;
Home 
Work
School
Moon
;
run;
proc sql noprint;
create table Joined as 
select place, number 
from Table2 Left Join Table1
on spedis( upcase(Table2.Place) , upcase(Table1.Location) ) < 10;
quit;

[PGStats]

You could use the truncated equality operator (eqt, the SQL equivalent to := in the data step) :

proc sql;
select place, number 
from Table2 Left Join Table1
on upcase(Table2.Place) eqt upcase(Table1.Location);
quit;

of course, this won't make "foot" equal "feet" 😁