topic Re: Two people with the same names (Last name, first name, and middle initials) after concatenation in SAS Programming

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

Josh12 — Wed, 30 Mar 2022 02:11:47 GMT

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

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

mkeintz — Wed, 30 Mar 2022 03:30:47 GMT

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

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

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

Tom — Wed, 30 Mar 2022 03:45:34 GMT

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.

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

Josh12 — Wed, 30 Mar 2022 03:47:02 GMT

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).

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

Astounding — Wed, 30 Mar 2022 03:58:33 GMT

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

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

ballardw — Wed, 30 Mar 2022 14:49:55 GMT

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.

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

mkeintz — Wed, 30 Mar 2022 15:06:39 GMT

@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.

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

ballardw — Wed, 30 Mar 2022 17:23:54 GMT

@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.