BookmarkSubscribeRSS Feed
Shakti_Sourav
Quartz | Level 8

Dear Team, 

 

I have created one data job and tried to load only duplicate records which is matching the Member Name, Father Name, Mother Name, Spouse Name (included null also), and Age (it can be vary).

 

One Sample:

Table: 1

Member Name      Father Name           Mother Name             Spouse Name                     Age

Shakti                      Rabi                         Asha                            Null                                   25

Sourav                     Mohan                      Madhu                         Rasmita                           30

 

Table2:

Member Name      Father Name             Mother Name               Spouse Name             Age

Shaakti                    Rabbi                          Asha                             Mohini                       24

 

Note: In table 1, Member name Shakti and table2 Shaakti both are same.

 

What I have done described below,

First, I have appended table 1 and Table2

second, then created match code like

MEMBER NAME : Name (Definition), 80% Sensitivity

FATHER NAME :  Name (Definition), 80% Sensitivity

Mother Name : Name (Definition), 80% Sensitivity

Age : Text (Definition), 80% Sensitivity.

 

I Want duplicate data like this:

Member Name      Father Name             Mother Name               Spouse Name             Age

Shaakti                    Rabbi                          Asha                             Mohini                       24

 

Unique is: 

Member Name      Father Name           Mother Name             Spouse Name                     Age

Shakti                      Rabi                         Asha                            Null                                   25

Sourav                     Mohan                      Madhu                         Rasmita                           30

 

But I got  this:

Member Name      Father Name           Mother Name             Spouse Name                     Age

Shakti                      Rabi                         Asha                            Null                                   25

Sourav                     Mohan                      Madhu                         Rasmita                           30

Shaakti                    Rabbi                          Asha                             Mohini                          24

 

Please suggest me How to handle Null values in Match Codes and how to identify Age code it may be +5 or -5 . if actual age is 20 some beneficiaries tried to fraud by age like 21 or 23.

please let me know, if  required further details.

Thank You

Shakti

4 REPLIES 4
audrey
SAS Employee

Hi,

 

Regarding the null/blank values, did you try to use the following options in the Match Code node:

-> Generate null match codes for blank field values

-> Preserve null values?

 

Regarding the age, it will not work to use a Text type match code definition.

You might want to create a simple function in Expression node to return true/false if the age is in a 10 years range.

 

Hope this helps.

Audrey

www.sas.comsupport.sas.com
SAS®... THE POWER TO KNOW®
Shakti_Sourav
Quartz | Level 8

Thank you !!!

 

I have taken Date of Birth as match code definition. it will works or not ? 

RacheLGomez123
Fluorite | Level 6
First you need to add match codes to your database lookup table (the one that the SQL Lookup node references). Create name match codes and insert them into this table. Then when you are testing with the external data provider node, add a match code node for the name (using the same definition and sensitivity as you used in appending match codes to your lookup table.). When you are testing perform the SQL lookup on the match codes and not on the exact names and the following results will return Mike = Michael; MIKE = Michael; mike = MICHAEL.

Regards,
Rachel Gomez

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 681 views
  • 0 likes
  • 4 in conversation