topic Re: Match on name issue in SAS Programming

Match on name issue

squeakums — Wed, 10 Jan 2024 18:01:25 GMT

I am fairly new to SAS and I have 2 queries that I am trying to merge together and I'm joining by a full outer join. The names are almost the same except 1 set of data has additional space and parentheses next to the name. So, the code is not finding all of the names:

How would I edit the code below to shorten the 'Edited Name' to shorten it if it has a space and parentheses so that they will match better. There is another item I can match on which is the SerID but issue is when I add this in the count distinct messes up so I'm trying to figure out if either I can join on data that isn't in the table or join on a name that sort of matches and will if edited in the formula below.

PROC SQL;
CREATE TABLE WORK.TEST AS
SELECT DISTINCT t1.'Edited Name'n,
t2.'N_DISTINCT_of_SerID'n,
t1.'N_DISTINCT_of_OLH: SupID'n
FROM WORK.TEST t1
FULL JOIN WORK.QUERY_FOR_TEST t2 ON (t1.'Edited Name'n = t2.'OrigName'n)
ORDER BY t1.'N_DISTINCT_of_OLH: SupID'n DESC;
QUIT;

Re: Match on name issue

yabwon — Wed, 10 Jan 2024 18:31:51 GMT

How about:

on compress(t1.'Edited Name'n,"( )") = compress(t2.'OrigName'n,"( )")

the second argument to compress() function means "delete all spaces and parenthesis".

Bart

Re: Match on name issue

ballardw — Wed, 10 Jan 2024 18:31:58 GMT

Removing parentheses is easy, the Compress function will do that. The question is where is the extra space and would we recognize it as such?

You should provide examples.

And if the "count" gets messed up when you have another identification variable then perhaps you don't want to match on only the name as names are unlikely to be unique in any largish real world source, especially if only looking at a first and last name. How do you know that possibly duplicated names are not different people if not looking at other information?

Re: Match on name issue

squeakums — Wed, 10 Jan 2024 18:37:33 GMT

Where would I add this in the code please?

Re: Match on name issue

SASKiwi — Wed, 10 Jan 2024 22:50:22 GMT

You just replace your ON condition in your posted example with @yabwon 's.

Re: Match on name issue

ChrisNZ — Wed, 20 Mar 2024 08:32:57 GMT

You don't specify where the extra space is. Note that trailing spaces are ignored when joining string values.