topic Re: same person with different name in New SAS User

same person with different name

dapenDaniel — Wed, 27 Nov 2019 16:09:30 GMT

Hi SAS experts!

I have a dataset as below.

FirmID Name

10001 Smith, Bob Alpha

10001 Smith, Bob A.

10002 Bloomberg, Jack Beta

10002 Bloomberg, Jack B.

The first two names are actually for the same person and the last two names are also for the same person. The logic is if one name (like Bloomgerg, Jack B.) is contained in the full name (like Bloomberg, Jack Beta) in the same company, then delete the short name and keep the completed name. Is there any way that I can get the dataset like below?

FirmID Name

10001 Smith, Bob Alpha

10002 Bloomberg, Jack Beta

Thanks!

Re: same person with different name

PeterClemmensen — Wed, 27 Nov 2019 06:22:21 GMT

What is the logic here? If a name is contained in another name within the same FirmID, then consider as the same Name?

Re: same person with different name

andreas_lds — Wed, 27 Nov 2019 06:23:28 GMT

If you can define the rules when to keep which observation, i am sure that code can be written to do so. From the data you have posted, i don't see a rule explaining why for FirmID 10001 the first name is taken, and for the other FirmID the second name.

Re: same person with different name

Ksharp — Wed, 27 Nov 2019 11:53:47 GMT

There are too many things you need to consider about.

Here could get you a start .

data have;
input FirmID                 Name $40.;
cards;
10001           Smith, Bob Alpha
10001           Smith, Bob A.
10002           Bloomberg, Jack Beta
10002           Bloomberg, Jack B.
;
proc sql;
select distinct a.*
 from have as a,have as b
  where a.FirmID=b.FirmID and a.Name ne b.Name and
   a.Name contains strip(substr(b.Name,1,length(b.name)-1));
quit;

Re: same person with different name

dapenDaniel — Wed, 27 Nov 2019 16:10:39 GMT

Hi @andreas_lds

Thanks for your reply. I have revised my question. The logic is if one name (like Bloomgerg, Jack B.) is contained in the full name (like Bloomberg, Jack Beta) in the same company, then delete the short name and keep the completed name.

Re: same person with different name

dapenDaniel — Wed, 27 Nov 2019 16:11:54 GMT

Hi @PeterClemmensen

Thanks for your reply. You are correct. I also revised my question further. The logic is if one name (like Bloomgerg, Jack B.) is contained in the full name (like Bloomberg, Jack Beta) in the same company, then delete the short name and keep the completed name.

Re: same person with different name

mkeintz — Wed, 27 Nov 2019 21:45:42 GMT

Can we presume that last names will always match exactly for any given person? So we only need to do "contains" tests for the rest of each name?

And are all the realizations of a "contain" situations in which the shorter name exactly matches the first part to the remaining name? Or can you have

Smith, B. James

Smith, Brian James

Re: same person with different name

sustagens — Thu, 28 Nov 2019 07:20:04 GMT

If all the shortened names have a period in your data and your actual scenario is as simple as your sample, then you can just eliminate all rows that contain '.'

PROC SQL;
   CREATE TABLE WANT AS 
   SELECT FirmID, 
          Name
      FROM HAVE
      WHERE (Name NOT CONTAINS '.');
QUIT;

Re: same person with different name

andreas_lds — Thu, 28 Nov 2019 06:32:38 GMT

@dapenDaniel wrote:

Hi @andreas_lds

Thanks for your reply. I have revised my question. The logic is if one name (like Bloomgerg, Jack B.) is contained in the full name (like Bloomberg, Jack Beta) in the same company, then delete the short name and keep the completed name.

Maybe this is a language problem, but

Bloomberg, Jack B.

is not part of

Bloomberg, Jack Beta

At least not, if punctuation marks are not removed before comparison.