comparing two variable and if same then 'Yes'

[Sandeep77]

Hi all,

I have two datasets and both of them have first name, surname and date of birth. I want to compare both of them and if the name is same in first dataset and second dataset then the output result should show 'Yes'. This will show me the data quality. Name of the first dataset is Cust_info and the second dataset is Name_check. Can I use case when statement to find? I would like the name in cust_info and the name in name_check to match using icustomerid and if the condition fulfils then 'Yes'.

Sample data for name match:

Data name_match;

infile cards expandtabs;

input icustomerid debt_code rep_code $ dr_inits $ dr_name $ COUNT DOB_match Name_match;

datalines ;

52844 249973926 899 Balal Sadiq 2 1 1

52844 288088438 891S Balal Sadiq 2 1 1

59888 402733356 168 Carla Reddell 1 0 0

75560 362107559 3B19 Doris Sweeney 1 1 0

75560 337169379 3B19 Dorris Sweeney 3 1 1

75560 339164279 3B19 Dorris Tambula 4 1 1

75560 395878135 3B19 Dorris Tambula 4 1 1;

run;

proc sql;
create table Name_match as 
select a.*,
b.dr_inits as Forename,
b.dr_name as Surname,
b.Date_of_birth as DOB,
b.count,
b.DOB_match,
b.Name_match
from Cust_info as a
inner join work.name_check as b on a.icustomerid  = b.icustomerid 
where b.Name_match = 1;
quit;
 

 Thanks

[Ksharp · Accepted solution]

/*
Yes. you can get it by PROC SQL.
But it is not right tool to do this, I would prefer to Hash Table.
*/

Data name_match;
infile cards expandtabs;
input icustomerid debt_code rep_code $ dr_inits $ dr_name $ COUNT DOB_match Name_match;
datalines ;
52844 249973926 899 Balal Sadiq 2 1 1
52844 288088438 891S Balal Sadiq 2 1 1
59888 402733356 168 Carla Reddell 1 0 0
;
run;

Data Cust_info;
infile cards expandtabs;
input icustomerid debt_code rep_code $ dr_inits $ dr_name $ COUNT DOB_match Name_match;
datalines ;
52844 249973926 899 Balal Sadiq 2 1 1
52844 288088438 891S Balal Sadiq 2 1 1
59888 402733356 168 Carla Reddell 1 0 0
75560 362107559 3B19 Doris Sweeney 1 1 0
75560 337169379 3B19 Dorris Sweeney 3 1 1
75560 339164279 3B19 Dorris Tambula 4 1 1
75560 395878135 3B19 Dorris Tambula 4 1 1
;
run;
 
proc sql;
create table want as
select *,case when exists(select * from name_match where icustomerid=a.icustomerid) then 'Yes' else 'No' end as want
 from Cust_info as a;
quit;