BookmarkSubscribeRSS Feed
deleted_user
Not applicable
data diab.medemerge_miss2;
set diab.medemerge_miss;

Last = substr(Last_Name,1,3);
First = substr(First_Name,1,1);
run;

proc sql ;
select Last into :last1 - :last131 from diab.medemerge_miss2;
quit;

proc sql ;
select First into :First1 - :First131 from diab.medemerge_miss2;
quit;

proc sql ;
select DOB into :DOB1 - :DOB131 from diab.medemerge_miss2;
quit;

data diab.medemerge3;
set diab.member_extract;
where
(index (FIRST_Name,"&First1") > 0 and index (LAST_Name,"&Last1") > 0 and DOB = "&DOB1"d)
or (index (FIRST_Name,"&First2") > 0 and index (LAST_Name,"&Last2") > 0 and DOB = "&DOB2"d)
or (index (FIRST_Name,"&First3") > 0 and index (LAST_Name,"&Last3") > 0 and DOB = "&DOB3"d)
or (index (FIRST_Name,"&First4") > 0 and index (LAST_Name,"&Last4") > 0 and DOB = "&DOB4"d)
or (index (FIRST_Name,"&First5") > 0 and index (LAST_Name,"&Last5") > 0 and DOB = "&DOB5"d)
or (index (FIRST_Name,"&First6") > 0 and index (LAST_Name,"&Last6") > 0 and DOB = "&DOB6"d)
or (index (FIRST_Name,"&First7") > 0 and index (LAST_Name,"&Last7") > 0 and DOB = "&DOB7"d)
or (index (FIRST_Name,"&First8") > 0 and index (LAST_Name,"&Last8") > 0 and DOB = "&DOB8"d)
or (index (FIRST_Name,"&First9") > 0 and index (LAST_Name,"&Last9") > 0 and DOB = "&DOB9"d)
or (index (FIRST_Name,"&First10") > 0 and index (LAST_Name,"&Last10") > 0 and DOB = "&DOB10"d)
or (index (FIRST_Name,"&First11") > 0 and index (LAST_Name,"&Last11") > 0 and DOB = "&DOB11"d)
or (index (FIRST_Name,"&First12") > 0 and index (LAST_Name,"&Last12") > 0 and DOB = "&DOB12"d)
or (index (FIRST_Name,"&First13") > 0 and index (LAST_Name,"&Last13") > 0 and DOB = "&DOB13"d)
or (index (FIRST_Name,"&First14") > 0 and index (LAST_Name,"&Last14") > 0 and DOB = "&DOB14"d)
or (index (FIRST_Name,"&First15") > 0 and index (LAST_Name,"&Last15") > 0 and DOB = "&DOB15"d)
or (index (FIRST_Name,"&First16") > 0 and index (LAST_Name,"&Last16") > 0 and DOB = "&DOB16"d)
or (index (FIRST_Name,"&First17") > 0 and index (LAST_Name,"&Last17") > 0 and DOB = "&DOB17"d)
or (index (FIRST_Name,"&First18") > 0 and index (LAST_Name,"&Last18") > 0 and DOB = "&DOB18"d)
or (index (FIRST_Name,"&First19") > 0 and index (LAST_Name,"&Last19") > 0 and DOB = "&DOB19"d)
or (index (FIRST_Name,"&First20") > 0 and index (LAST_Name,"&Last20") > 0 and DOB = "&DOB20"d)
or (index (FIRST_Name,"&First21") > 0 and index (LAST_Name,"&Last21") > 0 and DOB = "&DOB21"d)
or (index (FIRST_Name,"&First22") > 0 and index (LAST_Name,"&Last22") > 0 and DOB = "&DOB22"d)
or (index (FIRST_Name,"&First23") > 0 and index (LAST_Name,"&Last23") > 0 and DOB = "&DOB23"d)
or (index (FIRST_Name,"&First24") > 0 and index (LAST_Name,"&Last24") > 0 and DOB = "&DOB24"d)
or (index (FIRST_Name,"&First25") > 0 and index (LAST_Name,"&Last25") > 0 and DOB = "&DOB25"d)
or (index (FIRST_Name,"&First26") > 0 and index (LAST_Name,"&Last26") > 0 and DOB = "&DOB26"d)
or (index (FIRST_Name,"&First27") > 0 and index (LAST_Name,"&Last27") > 0 and DOB = "&DOB27"d)
or (index (FIRST_Name,"&First28") > 0 and index (LAST_Name,"&Last28") > 0 and DOB = "&DOB28"d)
or (index (FIRST_Name,"&First29") > 0 and index (LAST_Name,"&Last29") > 0 and DOB = "&DOB29"d)
or (index (FIRST_Name,"&First30") > 0 and index (LAST_Name,"&Last30") > 0 and DOB = "&DOB30"d)
or (index (FIRST_Name,"&First31") > 0 and index (LAST_Name,"&Last31") > 0 and DOB = "&DOB31"d)
or (index (FIRST_Name,"&First32") > 0 and index (LAST_Name,"&Last32") > 0 and DOB = "&DOB32"d)
or (index (FIRST_Name,"&First33") > 0 and index (LAST_Name,"&Last33") > 0 and DOB = "&DOB33"d)
or (index (FIRST_Name,"&First34") > 0 and index (LAST_Name,"&Last34") > 0 and DOB = "&DOB34"d)
or (index (FIRST_Name,"&First35") > 0 and index (LAST_Name,"&Last35") > 0 and DOB = "&DOB35"d)
or (index (FIRST_Name,"&First36") > 0 and index (LAST_Name,"&Last36") > 0 and DOB = "&DOB36"d)
or (index (FIRST_Name,"&First37") > 0 and index (LAST_Name,"&Last37") > 0 and DOB = "&DOB37"d)
or (index (FIRST_Name,"&First38") > 0 and index (LAST_Name,"&Last38") > 0 and DOB = "&DOB38"d)
or (index (FIRST_Name,"&First39") > 0 and index (LAST_Name,"&Last39") > 0 and DOB = "&DOB39"d)
or (index (FIRST_Name,"&First40") > 0 and index (LAST_Name,"&Last40") > 0 and DOB = "&DOB40"d)
or (index (FIRST_Name,"&First41") > 0 and index (LAST_Name,"&Last41") > 0 and DOB = "&DOB41"d)
or (index (FIRST_Name,"&First42") > 0 and index (LAST_Name,"&Last42") > 0 and DOB = "&DOB42"d)
or (index (FIRST_Name,"&First43") > 0 and index (LAST_Name,"&Last43") > 0 and DOB = "&DOB43"d)
or (index (FIRST_Name,"&First44") > 0 and index (LAST_Name,"&Last44") > 0 and DOB = "&DOB44"d)
or (index (FIRST_Name,"&First45") > 0 and index (LAST_Name,"&Last45") > 0 and DOB = "&DOB45"d)
or (index (FIRST_Name,"&First46") > 0 and index (LAST_Name,"&Last46") > 0 and DOB = "&DOB46"d)
or (index (FIRST_Name,"&First47") > 0 and index (LAST_Name,"&Last47") > 0 and DOB = "&DOB47"d)
or (index (FIRST_Name,"&First48") > 0 and index (LAST_Name,"&Last48") > 0 and DOB = "&DOB48"d)
or (index (FIRST_Name,"&First49") > 0 and index (LAST_Name,"&Last49") > 0 and DOB = "&DOB49"d)
or (index (FIRST_Name,"&First50") > 0 and index (LAST_Name,"&Last50") > 0 and DOB = "&DOB50"d)
or (index (FIRST_Name,"&First51") > 0 and index (LAST_Name,"&Last51") > 0 and DOB = "&DOB51"d)
or (index (FIRST_Name,"&First52") > 0 and index (LAST_Name,"&Last52") > 0 and DOB = "&DOB52"d)
or (index (FIRST_Name,"&First53") > 0 and index (LAST_Name,"&Last53") > 0 and DOB = "&DOB53"d)
or (index (FIRST_Name,"&First54") > 0 and index (LAST_Name,"&Last54") > 0 and DOB = "&DOB54"d)
or (index (FIRST_Name,"&First55") > 0 and index (LAST_Name,"&Last55") > 0 and DOB = "&DOB55"d)
or (index (FIRST_Name,"&First56") > 0 and index (LAST_Name,"&Last56") > 0 and DOB = "&DOB56"d)
or (index (FIRST_Name,"&First57") > 0 and index (LAST_Name,"&Last57") > 0 and DOB = "&DOB57"d)
or (index (FIRST_Name,"&First58") > 0 and index (LAST_Name,"&Last58") > 0 and DOB = "&DOB58"d)
or (index (FIRST_Name,"&First59") > 0 and index (LAST_Name,"&Last59") > 0 and DOB = "&DOB59"d)
or (index (FIRST_Name,"&First60") > 0 and index (LAST_Name,"&Last60") > 0 and DOB = "&DOB60"d)
or (index (FIRST_Name,"&First61") > 0 and index (LAST_Name,"&Last61") > 0 and DOB = "&DOB61"d)
or (index (FIRST_Name,"&First62") > 0 and index (LAST_Name,"&Last62") > 0 and DOB = "&DOB62"d)
or (index (FIRST_Name,"&First63") > 0 and index (LAST_Name,"&Last63") > 0 and DOB = "&DOB63"d)
or (index (FIRST_Name,"&First64") > 0 and index (LAST_Name,"&Last64") > 0 and DOB = "&DOB64"d)
or (index (FIRST_Name,"&First65") > 0 and index (LAST_Name,"&Last65") > 0 and DOB = "&DOB65"d)
or (index (FIRST_Name,"&First66") > 0 and index (LAST_Name,"&Last66") > 0 and DOB = "&DOB66"d)
or (index (FIRST_Name,"&First67") > 0 and index (LAST_Name,"&Last67") > 0 and DOB = "&DOB67"d)
or (index (FIRST_Name,"&First68") > 0 and index (LAST_Name,"&Last68") > 0 and DOB = "&DOB68"d)
or (index (FIRST_Name,"&First69") > 0 and index (LAST_Name,"&Last69") > 0 and DOB = "&DOB69"d)
or (index (FIRST_Name,"&First70") > 0 and index (LAST_Name,"&Last70") > 0 and DOB = "&DOB70"d)
or (index (FIRST_Name,"&First71") > 0 and index (LAST_Name,"&Last71") > 0 and DOB = "&DOB71"d)
or (index (FIRST_Name,"&First72") > 0 and index (LAST_Name,"&Last72") > 0 and DOB = "&DOB72"d)
or (index (FIRST_Name,"&First73") > 0 and index (LAST_Name,"&Last73") > 0 and DOB = "&DOB73"d)
or (index (FIRST_Name,"&First74") > 0 and index (LAST_Name,"&Last74") > 0 and DOB = "&DOB74"d)
or (index (FIRST_Name,"&First75") > 0 and index (LAST_Name,"&Last75") > 0 and DOB = "&DOB75"d)
or (index (FIRST_Name,"&First76") > 0 and index (LAST_Name,"&Last76") > 0 and DOB = "&DOB76"d)
or (index (FIRST_Name,"&First77") > 0 and index (LAST_Name,"&Last77") > 0 and DOB = "&DOB77"d)
or (index (FIRST_Name,"&First78") > 0 and index (LAST_Name,"&Last78") > 0 and DOB = "&DOB78"d)
or (index (FIRST_Name,"&First79") > 0 and index (LAST_Name,"&Last79") > 0 and DOB = "&DOB79"d)
or (index (FIRST_Name,"&First80") > 0 and index (LAST_Name,"&Last80") > 0 and DOB = "&DOB80"d)
or (index (FIRST_Name,"&First81") > 0 and index (LAST_Name,"&Last81") > 0 and DOB = "&DOB81"d)
or (index (FIRST_Name,"&First82") > 0 and index (LAST_Name,"&Last82") > 0 and DOB = "&DOB82"d)
or (index (FIRST_Name,"&First83") > 0 and index (LAST_Name,"&Last83") > 0 and DOB = "&DOB83"d)
or (index (FIRST_Name,"&First84") > 0 and index (LAST_Name,"&Last84") > 0 and DOB = "&DOB84"d)
or (index (FIRST_Name,"&First85") > 0 and index (LAST_Name,"&Last85") > 0 and DOB = "&DOB85"d)
or (index (FIRST_Name,"&First86") > 0 and index (LAST_Name,"&Last86") > 0 and DOB = "&DOB86"d)
or (index (FIRST_Name,"&First87") > 0 and index (LAST_Name,"&Last87") > 0 and DOB = "&DOB87"d)
or (index (FIRST_Name,"&First88") > 0 and index (LAST_Name,"&Last88") > 0 and DOB = "&DOB88"d)
or (index (FIRST_Name,"&First89") > 0 and index (LAST_Name,"&Last89") > 0 and DOB = "&DOB89"d)
or (index (FIRST_Name,"&First90") > 0 and index (LAST_Name,"&Last90") > 0 and DOB = "&DOB90"d)
or (index (FIRST_Name,"&First91") > 0 and index (LAST_Name,"&Last91") > 0 and DOB = "&DOB91"d)
or (index (FIRST_Name,"&First92") > 0 and index (LAST_Name,"&Last92") > 0 and DOB = "&DOB92"d)
or (index (FIRST_Name,"&First93") > 0 and index (LAST_Name,"&Last93") > 0 and DOB = "&DOB93"d)
or (index (FIRST_Name,"&First94") > 0 and index (LAST_Name,"&Last94") > 0 and DOB = "&DOB94"d)
or (index (FIRST_Name,"&First95") > 0 and index (LAST_Name,"&Last95") > 0 and DOB = "&DOB95"d)
or (index (FIRST_Name,"&First96") > 0 and index (LAST_Name,"&Last96") > 0 and DOB = "&DOB96"d)
or (index (FIRST_Name,"&First97") > 0 and index (LAST_Name,"&Last97") > 0 and DOB = "&DOB97"d)
or (index (FIRST_Name,"&First98") > 0 and index (LAST_Name,"&Last98") > 0 and DOB = "&DOB98"d)
or (index (FIRST_Name,"&First99") > 0 and index (LAST_Name,"&Last99") > 0 and DOB = "&DOB99"d)
or (index (FIRST_Name,"&First100") > 0 and index (LAST_Name,"&Last100") > 0 and DOB = "&DOB100"d)
or (index (FIRST_Name,"&First101") > 0 and index (LAST_Name,"&Last101") > 0 and DOB = "&DOB101"d)
or (index (FIRST_Name,"&First102") > 0 and index (LAST_Name,"&Last102") > 0 and DOB = "&DOB102"d)
or (index (FIRST_Name,"&First103") > 0 and index (LAST_Name,"&Last103") > 0 and DOB = "&DOB103"d)
or (index (FIRST_Name,"&First104") > 0 and index (LAST_Name,"&Last104") > 0 and DOB = "&DOB104"d)
or (index (FIRST_Name,"&First105") > 0 and index (LAST_Name,"&Last105") > 0 and DOB = "&DOB105"d)
or (index (FIRST_Name,"&First106") > 0 and index (LAST_Name,"&Last106") > 0 and DOB = "&DOB106"d)
or (index (FIRST_Name,"&First107") > 0 and index (LAST_Name,"&Last107") > 0 and DOB = "&DOB107"d)
or (index (FIRST_Name,"&First108") > 0 and index (LAST_Name,"&Last108") > 0 and DOB = "&DOB108"d)
or (index (FIRST_Name,"&First109") > 0 and index (LAST_Name,"&Last109") > 0 and DOB = "&DOB109"d)
or (index (FIRST_Name,"&First110") > 0 and index (LAST_Name,"&Last110") > 0 and DOB = "&DOB110"d)
or (index (FIRST_Name,"&First111") > 0 and index (LAST_Name,"&Last111") > 0 and DOB = "&DOB111"d)
or (index (FIRST_Name,"&First112") > 0 and index (LAST_Name,"&Last112") > 0 and DOB = "&DOB112"d)
or (index (FIRST_Name,"&First113") > 0 and index (LAST_Name,"&Last113") > 0 and DOB = "&DOB113"d)
or (index (FIRST_Name,"&First114") > 0 and index (LAST_Name,"&Last114") > 0 and DOB = "&DOB114"d)
or (index (FIRST_Name,"&First115") > 0 and index (LAST_Name,"&Last115") > 0 and DOB = "&DOB115"d)
or (index (FIRST_Name,"&First116") > 0 and index (LAST_Name,"&Last116") > 0 and DOB = "&DOB116"d)
or (index (FIRST_Name,"&First117") > 0 and index (LAST_Name,"&Last117") > 0 and DOB = "&DOB117"d)
or (index (FIRST_Name,"&First118") > 0 and index (LAST_Name,"&Last118") > 0 and DOB = "&DOB118"d)
or (index (FIRST_Name,"&First119") > 0 and index (LAST_Name,"&Last119") > 0 and DOB = "&DOB119"d)
or (index (FIRST_Name,"&First120") > 0 and index (LAST_Name,"&Last120") > 0 and DOB = "&DOB120"d)
or (index (FIRST_Name,"&First121") > 0 and index (LAST_Name,"&Last121") > 0 and DOB = "&DOB121"d)
or (index (FIRST_Name,"&First122") > 0 and index (LAST_Name,"&Last122") > 0 and DOB = "&DOB122"d)
or (index (FIRST_Name,"&First123") > 0 and index (LAST_Name,"&Last123") > 0 and DOB = "&DOB123"d)
or (index (FIRST_Name,"&First124") > 0 and index (LAST_Name,"&Last124") > 0 and DOB = "&DOB124"d)
or (index (FIRST_Name,"&First125") > 0 and index (LAST_Name,"&Last125") > 0 and DOB = "&DOB125"d)
or (index (FIRST_Name,"&First126") > 0 and index (LAST_Name,"&Last126") > 0 and DOB = "&DOB126"d)
or (index (FIRST_Name,"&First127") > 0 and index (LAST_Name,"&Last127") > 0 and DOB = "&DOB127"d)
or (index (FIRST_Name,"&First128") > 0 and index (LAST_Name,"&Last128") > 0 and DOB = "&DOB128"d)
or (index (FIRST_Name,'&First129') > 0 and index (LAST_Name,'&Last129') > 0 and DOB = "&DOB129"d)
or (index (FIRST_Name,'&First130') > 0 and index (LAST_Name,'&Last130') > 0 and DOB = "&DOB130"d)
or (index (FIRST_Name,'&First131') > 0 and index (LAST_Name,'&Last131') > 0 and DOB = "&DOB131"d)
;
run;
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
A better suggestion is to explain what you need to accomplish aside from the code snippet you have provided.

Honestly, from the code perspective, it appears that you are comparing the first 3 characters of each observation's Last_Name (not each DISTINCT prefix substring) and the first 1 character of each observations First_Name (again not DISTINCT) against (scanning) the entire Last_Name and First_Name but only for an particular date. For me, this doesn't make reasonable sense, so maybe you might want to explain what you are trying to accomplish for most effective feedback.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
I had to increase the First variable to substring 3 characters instead of the too restrictive 1.

Basically I want to match members from one dataset to another. The problem is our clients use their own unique identifier and we have our own. (Since we have thousands of clients, agreeing upon a universal unique identifier is cumbersome) Therefore, I have to resort to some primitave matching algorithm. Matching on the entire first name and last name is too restrictive and will miss many matches b/c of inherent misspellings, middle name abbreviation appends, and surname suffix appends to either the first or last name. Therefore I resorted to substringing part of the first and last name and requiring an exact match on birth date. This method works fine, however I need to automate this somehow as I expect many more client lists to match.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Personal opinion: you're worse off with the substring-prefix and INDEX scan attempt, frankly. Suggest you attempt to treat the problem by determining some key to match the data sources. That's all I can recommend, in good conscience.

Scott Barry
SBBWorks, Inc.
Doc_Duke
Rhodochrosite | Level 12
Like Scott said, you need to be more descriptive on your data structures and goals than just providing code. Perhaps this, untested code, might work:

data medemerge_miss2;
set diab.medemerge_miss;
Key= substr(Last_Name,1,3)||substr(First_Name,1,1)||PUT(dob,mmddyy10.);
KEEP key;
run;

data medemerge2;
set diab.member_extract;
Key= substr(Last_Name,1,3)||substr(First_Name,1,1)||PUT(dob,mmddyy10.);
run;

PROC SQL;
CREATE TABLE diab.mdemerge3 AS
SELECT *
FROM diab.member_extract AS e,
medemerge_miss2 AS m
WHERE e.key=m.key;
QUIT; RUN;

The idea was to create a unique key in each table that can be used in join. You might be able to do it directly with the SUBSTR's in the SQL, but there may also be a performance penalty there.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 4 replies
  • 890 views
  • 0 likes
  • 3 in conversation