topic Re: How to find similar and equal values in one column and then another column in SAS Programming

How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 12:15:23 GMT

num_A	num_B	name	birth_date	id
1234	abcd	M Rita Costa Santos	01/01/2000	1
3333	uvwx	M Rita Costa Santos	01/01/2000	1
5678	efgh	Maria Rita C Santos	01/01/2000
9101	ijkl	Rita Costa Santos	01/01/2000	1
1111	mnop	Maria Leonor Santos Silva	02/03/2001
2222	qrst	Leonor Santos Silva	02/03/2001	2
4444	yzab	Leonor Santos Silva	30/08/1999

Imagine I have this table, but in a large scale. I want to find similar and equal values in the name column and if they are similar/equal, go to the column birth_date and see if they are equal. If yes, create the same id for this cases. So i want the final output to be:

num_A	num_B	name	birth_date	id
1234	abcd	M Rita Costa Santos	01/01/2000	1
3333	uvwx	M Rita Costa Santos	01/01/2000	1
5678	efgh	Maria Rita C Santos	01/01/2000	1
9101	ijkl	Rita Costa Santos	01/01/2000	1
1111	mnop	Maria Leonor Santos Silva	02/03/2001	2
2222	qrst	Leonor Santos Silva	02/03/2001	2
4444	yzab	Leonor Santos Silva	30/08/1999	3

Thank you in advance.

Re: How to find similar and equal values in one column and then another column

PaigeMiller — Wed, 04 Nov 2020 12:27:05 GMT

Can you describe further what you mean by "similar"?

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Wed, 04 Nov 2020 12:30:44 GMT

What does 'similar' mean here? That eg the spelling distance is not far from each other?

Also, is the data sorted by these 'likewise' names?

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 12:42:09 GMT

Similar, like in the examples. For example, the birth_date is the same for these:
- Maria Rita Costa Santos

- M Rita Costa Santos, the M is an abbreviation for Maria

- Rita Costa Santos - because Maria is such a popular name, the person chooses to only say the second name, which is less common.

However, these are all the same person, but just wrote the name in different ways. However, in the last two examples, people can have the same name and be different people (check the birth date).

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 12:45:17 GMT

Please read what I answered to draycut, it was the same question. Thank you!

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Wed, 04 Nov 2020 12:47:17 GMT

And you already have the ID for some of the obs? But not all, correct?

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 12:50:53 GMT

yes, not all 🙂 the point is to have the ids according to the same people, so I can group the data correctly

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Wed, 04 Nov 2020 13:09:25 GMT

Ok. Is your data sorted like this? Or could M Rita Costa Santos have an observation at the bottom?

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 13:15:28 GMT

It is not sorted. I just showed this so it would be easier to understand. I have like 12 000 records of names.

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Wed, 04 Nov 2020 13:27:25 GMT

Can a person have no ID with a pre-assigned value?

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 13:32:19 GMT

Can you elaborate please?

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Wed, 04 Nov 2020 13:39:45 GMT

The posted data you have is this:

data have;
infile datalines missover;
input num_A num_B $ name $ 11-36 birth_date :ddmmyy10. id;
format birth_date ddmmyy10.;
datalines;
1234 abcd M Rita Costa Santos        01/01/2000 1 
3333 uvwx M Rita Costa Santos        01/01/2000 1 
5678 efgh Maria Rita C Santos        01/01/2000   
9101 ijkl Rita Costa Santos          01/01/2000 1 
1111 mnop Maria Leonor Santos Silva  02/03/2001   
2222 qrst Leonor Santos Silva        02/03/2001 2 
4444 yzab Leonor Santos Silva        30/08/1999   
;

Can a situation arise, where a person in your data have no ID attached to any of its observations? See the two last obs in the data below (no ID)

data have;
infile datalines missover;
input num_A num_B $ name $ 11-36 birth_date :ddmmyy10. id;
format birth_date ddmmyy10.;
datalines;
1234 abcd M Rita Costa Santos        01/01/2000 1 
3333 uvwx M Rita Costa Santos        01/01/2000 1 
5678 efgh Maria Rita C Santos        01/01/2000   
9101 ijkl Rita Costa Santos          01/01/2000 1 
1111 mnop Maria Leonor Santos Silva  02/03/2001   
2222 qrst Leonor Santos Silva        02/03/2001 2 
4444 yzab Leonor Santos Silva        30/08/1999   
xxxx xxxx Some Name Here             01/01/1960   
xxxx xxxx Some Name Here             01/01/1960   
;

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 13:47:21 GMT

The only blank field possible would be the ID

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Wed, 04 Nov 2020 13:59:08 GMT

I will try to make myself more clear. Consider the data below. This is the same data that you posted plus an additional person.

While the two people in your initial data both have a pre-assigned ID (the one we want to hit for the remaining obs for those people), "Donald J Trump" does not. He is obviously not equal to either id=1 or id=2.

Can this situation happen in your data? And then what?

data have;
infile datalines missover;
input num_A num_B $ name $ 11-36 birth_date :ddmmyy10. id;
format birth_date ddmmyy10.;
datalines;
1234 abcd M Rita Costa Santos        01/01/2000 1 
3333 uvwx M Rita Costa Santos        01/01/2000 1 
5678 efgh Maria Rita C Santos        01/01/2000   
9101 ijkl Rita Costa Santos          01/01/2000 1 
1111 mnop Maria Leonor Santos Silva  02/03/2001   
2222 qrst Leonor Santos Silva        02/03/2001 2 
4444 yzab Leonor Santos Silva        30/08/1999   
xxxx xxxx Donald J Trump             01/01/1960   
xxxx xxxx Donald J Trump             01/01/1960   
;

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 14:01:40 GMT

Ah yes, it can happen. That's why I gave the last example of a Leonor born in another date, which will have an id 3. The Donald Trump should be the id 4 for example.

Re: How to find similar and equal values in one column and then another column

fcf — Wed, 04 Nov 2020 14:25:27 GMT

Another way is to create another column with all the IDs, instead of putting the id in the same column

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Thu, 05 Nov 2020 11:40:13 GMT

Ok. This should do it

The overall logic is this:

Load the observations with id's into two lookup tables. One keyed by name and birth_date. The other keyed by just birth_date.
Read all data sequentially.
If ID is missing, look in the first table h1 for an exact match on name and birth_date. If not found, look in the second table h2 on all entries with the desired birth_date. If one of the names in h2 is appropriately close to the original name, set the ID to the lookup value of ID.
Add the 'new' name to h2.
If Id is still missing and not filled by either lookup above, set ID to (maximum value of ID + 1).

By appropriately close, I used the Complev Function and simply chose a value that works for this example. Ie here, the distance should be less than ten to be considered 'similar'. You may have to adjust that part.

@fcf Let me know if it works for you 🙂

Code:

data have;
infile datalines missover;
input num_A num_B $ name $ 11-36 birth_date :ddmmyy10. id;
format birth_date ddmmyy10.;
datalines;
1234 abcd M Rita Costa Santos        01/01/2000 1 
3333 uvwx M Rita Costa Santos        01/01/2000 1 
5678 efgh Maria Rita C Santos        01/01/2000   
9101 ijkl Rita Costa Santos          01/01/2000 1 
1111 mnop Maria Leonor Santos Silva  02/03/2001   
2222 qrst Leonor Santos Silva        02/03/2001 2 
4444 yzab Leonor Santos Silva        30/08/1999   
;

data want(keep = num_A num_B name birth_date id);
   format num_A num_B name birth_date id;
   if _N_ = 1 then do;
      dcl hash h1 ();
      h1.definekey("name", "birth_date");
      h1.definedata("i");
      h1.definedone();

      dcl hash h2 (multidata : "Y");
      h2.definekey("birth_date");
      h2.definedata("n", "i");
      h2.definedone();

      do until (z);
         set have(rename=(id=i name=n) where = (i)) end = z;
         h1.ref();
         h2.ref();
         maxid = max(maxid, i);
      end;
   end;

   set have;

   if id = . then do;
      if h1.find() ne 0 then do;
         do while (h2.do_over() = 0);
            if complev(name, n) < 10 then do;
               id = i;
               h1.ref(key : n, key : birth_date, data : id);
            end;
         end;
      end;
   end;

   if id = . then do;
      maxid + 1;
      id = maxid;
      h1.ref(key : name, key : birth_date, data : id);
   end;

run;

Result

num_A num_B name                       birth_date  id 
1234  abcd  M Rita Costa Santos        01/01/2000  1 
3333  uvwx  M Rita Costa Santos        01/01/2000  1 
5678  efgh  Maria Rita C Santos        01/01/2000  1 
9101  ijkl  Rita Costa Santos          01/01/2000  1 
1111  mnop  Maria Leonor Santos Silva  02/03/2001  2 
2222  qrst  Leonor Santos Silva        02/03/2001  2 
4444  yzab  Leonor Santos Silva        30/08/1999  3

Re: How to find similar and equal values in one column and then another column

fcf — Thu, 19 Nov 2020 10:10:23 GMT

Hi draycut! I'm sorry for taking so long to reply. It did not worked. Maybe because I may not have the same people in the following records(?) So the names might be scattered all over the table. I'm guessing that's why it is not working... I don't know...

Re: How to find similar and equal values in one column and then another column

PeterClemmensen — Thu, 19 Nov 2020 11:10:48 GMT

@fcf you have to be more specific than "It did not work". What did not work? How did the result differ from what you expect?

My code works regardless of the order on your data. That is not why.

Post a larger sample of your actual data and let me take a look.

Re: How to find similar and equal values in one column and then another column

fcf — Thu, 19 Nov 2020 11:19:15 GMT

I cannot post the actual data. It is private. It did not work - it always returned a different id.