Two people with the same names (Last name, first name, and middle initials) after concatenation

[Josh12]

Please, I need your help to solve a problem I had after I concatenated "Lastname, first name, and middle name. I want the characters to look like this, e.g., Johnson, Smart B. However, I was successful doing that, but I have two different people in different departments with the names (e.g., Smith, Johnson L maybe in English dept and another person bearing the same Smith, Johnson L in French Dept). Please, how can I solve this problem before joining the data set? How do I remove the initial from one of the names to make the concatenation or names unique by person?

Show less

[mkeintz]

You need a way to determine whether a tentative new name has already been established for another person.

Show us the code (as a complete data step, or complete proc sql)  that you are currently using - USING THE  

OR

  

ICONS.  That is essential to developing code to detect duplicates.

[Josh12]

data emp1; set emp;

length name $70.;

middle_name2=substr(middle_name,1,1);

name_MI=trim(last_name)||", "||trim(first_name)||" "||trim(middle_name2);

first_name_i=scan(first_name,1);

name=trim(last_name)||", "||trim(first_name_i);

run;

I have a base data that was set up already as (Lastname, Firstname middle initial like Smart, Johnson L). 

[Tom]

Don't bother making the NAME unique.  Create an ID variable that is unique.

Something like:

data want;
  set have;
  id+1;
  new_name = catx(', ',last_name,catx(' ',first_name,middle_initial));
run;

Then use their ID when combining the names with other data you have about them.

[Astounding · Accepted solution]

For the unique identifier, don't use NAME. use NAME plus DEPT.

[ballardw]

And depending on your data you may need to actually investigate that the name is not actually the same person. People change positions within organizations, sometimes frequently. If you data does not represent a single point in time, i.e. only 15 Mar 2022, but some interval then that person might occur as part of two different organizations.
Which is why a very large number of organizations use an Employee, or from your context, Student identifier instead of names.

[mkeintz]

@Astounding's suggestion is good for the current situation, assuming no department currently has two people with the same name.

But people change departments - and much less frequently they change names.  Either the identifier will remain fixed, but lose informational value (so why use name & dept to begin with?)   -  or it will be modified to follow the employee's changes, requiring a table of links between old and new identifiers.

There will be blood.

[ballardw]

---

@mkeintz wrote:

@Astounding's suggestion is good for the current situation, assuming no department currently has two people with the same name.

But people change departments - and much less frequently they change names.  Either the identifier will remain fixed, but lose informational value (so why use name & dept to begin with?)   -  or it will be modified to follow the employee's changes, requiring a table of links between old and new identifiers.

There will be blood.

---

Sounds like you may have bitten by the same things that I have. Including the "new database implementation will use new identifiers" headache.