It will be more difficult to match -- reliably -- something like "Jose Antonio" with "J Antonio". However, I have written some code that will do basic text matching. It still needs a bit of tuning. If you are a good SAS coder, you can adjust the code as you see fit. I'll in include the code below. Otherwise, I'll try to spend some more time with it on Thursday or Friday.
Jim
Results, immediately below. Luis Van Bob is incorrect. That case needs more work.
Code:
DATA Biographies;
INPUT City_Code & : $2.
Biography & : $256.
;
DATALINES;
ED Mr. Joseph Andrew is hardworking person.
ED Ms. Angela Blanc is genius.
ED Mrs. Sarah Greene was absent.
ED Mr. Rubin D. Elvin cleans the room.
TO Ms. Luis Fernandez is a teacher.
TO Mr. Jose Antonio works from home.
TO Mr. Luis Van Bob comes late.
CA Ms. Maria Jose travelled last week.
CA Mr. Philip Bursa will help you.
;
RUN;
DATA Name_And_Status;
DROP _:;
RETAIN _Max_Names 0;
INPUT City_Cd & : $2.
Name & : $64.
Status & : $32.
;
_Rec_Cnt + 1;
Name_Cnt = COUNTW(Name, ' ');
IF Name_Cnt > _Max_Names THEN
_Max_Names = Name_Cnt;
CALL SYMPUTX('Rec_Cnt', _Rec_Cnt, 'G');
CALL SYMPUTX('Max_Names', _Max_Names, 'G');
DATALINES;
ED Blanc Angela Teacher
ED Albert Crew Doctor
ED Rubin Elvin Engineer
ED Joseph T. Andrew Lawyer
TO Luis V. Bob Doctor
TO J. Antonio Teacher
TO Luis Fernandez Lawyer
CA Accent Andiron Worker
CA Maria Jose Antonio Student
;
RUN;
DATA Matched_Employment_Status;
DROP _:;
DROP City_Cd;
DROP Name;
DROP Status;
DROP Name_Cnt;
ARRAY Names [&Rec_Cnt, &Max_Names] $32 _TEMPORARY_;
ARRAY Statuses [&Rec_Cnt] $32 _TEMPORARY_;
ARRAY Codes [&Rec_Cnt] $2 _TEMPORARY_;
ARRAY Match [&Rec_Cnt] 3 _TEMPORARY_;
IF _N_ = 1 THEN
DO;
DO UNTIL (_End_Of_Names);
SET Name_And_Status END = _End_Of_Names;
_Name + 1;
Statuses[_Name] = Status;
Codes [_Name] = City_Cd;
DO _i = 1 TO Name_Cnt;
Names[_Name, _i] = COMPRESS(SCAN(Name, _i),,'KA');
* PUTLOG "&Nte1 " _Name= _i= Names[_Name, _i]= Statuses[_Name]= Codes[_Name]= ;
END;
END;
END;
* STOP;
SET Biographies;
PUTLOG "&Nte2 ";
_Match_Cnt = 0;
_Token_Cnt = COUNTW(Biography, ' ');
PUTLOG "&Nte1 " _N_= _Token_Cnt=;
DO _i = 1 TO _Token_Cnt;
DO _j = 1 TO &Rec_Cnt;
DO _k = 1 TO &Max_Names;
_Bio = COMPRESS(SCAN(Biography, _i),,'KA');
IF City_Code = Codes[_j] AND
SCAN(Biography, _i) = Names[_j, _k] THEN
DO;
_Match_Cnt + 1;
Match[_Match_Cnt] = _j;
END;
PUTLOG "&Nte2 " _N_= _i= _j= _k= _Bio= Statuses[_j]= Names[_j, _k]= _Match_Cnt=;
END;
IF _Match_Cnt >= 2 THEN
DO;
_Match = 1;
DO _l = 1 TO (_Match_Cnt - 1);
IF Match[_l] ^= Match[_l + 1] THEN
_Match = 1;
END;
IF _Match THEN
DO;
Employ_Status = Statuses[_j];
_j = &Rec_Cnt;
_i = _Token_Cnt;
END;
END;
END;
END;
RUN;
OK, a little sleep, and I spotted the deficiency in my program. I now have it matching to the correct people:
As I say, though, when your Dataset II (the master definition of who has what status) has only a first initial, it's going to be difficult to match against. It will be difficult to distinguish a J. Antonio from a Maria Jose Antonio, although a secondary match on City Code helps tremendously. There is always the danger of a false positive, i.e. associating someone with a Status that is not really theirs.
I set my matching to require two matches. For example, for the name "Luis Van Bob," you could have up to three matches ("Luis", "Van", and "Bob"). There has to be a minimum of two matches for the program to consider the match to be a positive name match. When trying to match, for example, "Luis Fernandez" with "Luis Van Bob", the match will be = 1 because only "Luis" matches. The program does not consider this to be a name match. However "Luis V Bob" and "Luis Van Bob" would be = 2 ("Luis" and "Bob"), and therefore the program would consider this a name match.
You could still get false positives, but city matching helps much in this regard. For example, Jose Antonio and Maria Jose Antonio could easily be falsely matched, but since Jose Antonio is from Toronto and Maria Jose Antonio is from Calgary, they will not be matched.
WARNING: Even matching on both city and name may still give some false positives. For really common names, there absolutely could be two people with the same first and last names in a large city. Name matching is never 100%.
We can increase the number of matches if we use something like the COMPGED function or the SPEDIS function, but this also increases our chances of false positives. See https://support.sas.com/resources/papers/proceedings14/1674-2014.pdf
The SAS code is below.
Jim
DATA Biographies;
INPUT City_Code & : $2.
Biography & : $256.
;
DATALINES;
ED Mr. Joseph Andrew is hardworking person.
ED Ms. Angela Blanc is genius.
ED Mrs. Sarah Greene was absent.
ED Mr. Rubin D. Elvin cleans the room.
TO Ms. Luis Fernandez is a teacher.
TO Mr. Jose Antonio works from home.
TO Mr. Luis Van Bob comes late.
CA Ms. Maria Jose travelled last week.
CA Mr. Philip Bursa will help you.
;
RUN;
**------------------------------------------------------------------------------**;
DATA Name_And_Status;
DROP _:;
RETAIN _Max_Names 0;
INPUT City_Cd & : $2.
Name & : $64.
Status & : $32.
;
_Rec_Cnt + 1;
Name_Cnt = COUNTW(Name, ' ');
IF Name_Cnt > _Max_Names THEN
_Max_Names = Name_Cnt;
CALL SYMPUTX('Rec_Cnt', _Rec_Cnt, 'G');
CALL SYMPUTX('Max_Names', _Max_Names, 'G');
DATALINES;
ED Blanc Angela Teacher
ED Albert Crew Doctor
ED Rubin Elvin Engineer
ED Joseph T. Andrew Lawyer
TO Luis V. Bob Doctor
TO J. Antonio Teacher
TO Luis Fernandez Lawyer
CA Accent Andiron Worker
CA Maria Jose Antonio Student
;
RUN;
**------------------------------------------------------------------------------**;
DATA Matched_Employment_Status;
DROP _:;
DROP City_Cd;
DROP Name;
DROP Status;
DROP Name_Cnt;
ARRAY Names [&Rec_Cnt, &Max_Names] $32 _TEMPORARY_;
ARRAY Statuses [&Rec_Cnt] $32 _TEMPORARY_;
ARRAY Codes [&Rec_Cnt] $2 _TEMPORARY_;
ARRAY Match [&Rec_Cnt] 3 _TEMPORARY_;
IF _N_ = 1 THEN
DO;
DO UNTIL (_End_Of_Names);
SET Name_And_Status END = _End_Of_Names;
_j + 1;
Statuses[_j] = Status;
Codes [_j] = City_Cd;
DO _k = 1 TO Name_Cnt;
Names[_j, _k] = COMPRESS(SCAN(Name, _k),,'KA');
END;
END;
END;
SET Biographies;
CALL MISSING (of Match[*]);
_Token_Cnt = COUNTW(Biography, ' ');
DO _i = 1 TO _Token_Cnt;
DO _j = 1 TO &Rec_Cnt;
DO _k = 1 TO &Max_Names;
_Bio = COMPRESS(SCAN(Biography, _i),,'KA');
IF City_Code = Codes[_j] AND
SCAN(Biography, _i) = Names[_j, _k] THEN
DO;
Match[_j] + 1;
END;
END;
END;
END;
_Max_Match = 0;
DO _j = 1 TO &Rec_Cnt;
IF Match[_j] >= 2 THEN
DO;
IF Match[_j] > _Max_Match THEN
DO;
Employ_Status = Statuses[_j];
_Max_Match = Match[_j];
END;
END;
END;
RUN;
Please run the following code;
%PUT NOTE: &=Rec_Cnt &=Max_Names;Before the data step with this code runs:
ARRAY Names [&Rec_Cnt, &Max_Names] $32 _TEMPORARY_;
Rec_Cnt and Max_Names need to be set to positive integers for the arrays to work. It looks like Rec_Cnt is not being set.
Please post the log, and please use the following buttons here on the forum for posting the log:
Jim
- Ask the Expert: Building Data Visualization Applications Using Python and SAS | 21-Jan-2025
- RTSUG Presents: SAS, SQL, R, and Python, We're All Friends | 23-Jan-2025
- Join MSUG for their January Free Webinar | 23-Jan-2025
- Ask the Expert: How Can I Geocode Address Data Using SAS® Viya®? | 28-Jan-2025
- San Diego SAS User Group (SANDS) Meeting | 29-Jan-2025
- Ask the Expert: Easily Turn Your Automated Explanation Into a Predictive Model | 06-Feb-2025
- Ask the Expert: Bist Du schon fit für KI? Aufbau von Daten- und Analyse-Kompetenz leicht gemacht. | 13-Feb-2025
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.
-
2084 views
-
11 likes
-
-
