topic Re: Proc sql Left Join where a value isnt exact? in SAS Programming

Proc sql Left Join where a value isnt exact?

mcook — Sat, 05 Dec 2020 01:11:04 GMT

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?

Re: Proc sql Left Join where a value isnt exact?

ChrisNZ — Sat, 05 Dec 2020 07:46:51 GMT

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

Re: Proc sql Left Join where a value isnt exact?

Ksharp — Sat, 05 Dec 2020 11:48:04 GMT

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;

Re: Proc sql Left Join where a value isnt exact?

mcook — Sat, 05 Dec 2020 15:02:20 GMT

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

Re: Proc sql Left Join where a value isnt exact?

PGStats — Sat, 05 Dec 2020 19:31:22 GMT

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" 😁

Re: Proc sql Left Join where a value isnt exact?

ChrisNZ — Sun, 06 Dec 2020 21:04:36 GMT

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.