topic fuzzy matching in SAS Programming

fuzzy matching

Jarvin99 — Thu, 20 Oct 2022 15:11:18 GMT

Hi all, I face an error during fuzzy merging, and attached are my two sample datasets.

Below is my code:

proc sql;
create table try
as select a.gvkey, a.comnam, b.*, compged(a.comnam, b.name) as compged_score
	from com2021 as a, sp as b
	where calculated compged_score le 50
	order by b.name;
quit;

The merged data 'try' is supposed to contain all variables filled, so I am not sure why some variables are empty. The log shows " The execution of this query involves performing one or more Cartesian product joins that can not be optimized."

I am not sure how to solve the log. Pls helps!

Re: fuzzy matching

ballardw — Thu, 20 Oct 2022 15:33:10 GMT

Look at your Com2021 data set. Lots of records have missing values for the Comnam variable. The first one for example.

The "not optimized" message is coming because you use

from com2021 as a, sp as b

That , says you a doing a Cartesian join. Every record in A with every record in B. There is no way to "optimize" such a forced join. This message belongs there.

When I run your code with those sets I get no records in the output as the smallest Compged_score is 110.

Re: fuzzy matching

Jarvin99 — Thu, 20 Oct 2022 17:09:31 GMT

What if I do not restrict? Why my 'country' variable is empty after merge?

Also, do you mean it is 1-to-1, so there is no optimization in this case?

Then, how should I do to obtain 'gvkey' from Com2021 for firms in Sp using firm names?

Thank you.

Re: fuzzy matching

Reeza — Thu, 20 Oct 2022 17:33:16 GMT

@Jarvin99 wrote:
Also, do you mean it is 1-to-1, so there is no optimization in this case?

It's a one to all/many join.

Every record from table A is joined to every record in Table B.

If you have 10 records in TableA and 20 records in TableB, there will 10*20 comparisons and 200 records generated if you do not filter the results. If in TableB, there are 3 empty rows, then there will be 10*3 = 30 empty records for the second variable in the data set.

If you have a large data set this can be very computationally intensive.