Probabilistic Record Linkage

[Amy]

Hi there:

I'm trying to link two large data sets collected from two different
sources for epidemiological research. These two data sets share a few
common fields such as Social Insurance Number, Name (partically),
Gender, etc. Due to incompleteness or errors of some data records, I
guess we have to use probabilistic linkage to find matched records.

Is there any academic/commercial software available for probabilistic
linkage? Or some SQL programming will do it?

Any pointers would be greatly appreciated.

Amy

[Gary Floam]

Amy,

If you could define a probability function and a threshold, you could have a
query looking something like

Select .... from Table1, Table2 where ...
and YourFunction(Table1.field1, Table1.field2....,Table2,field1,
Table2.field2...) > .75

This is only a beginning, but it may help.

Gary
"Amy" <ww**@shaw.ca> wrote in message
news:6c**************************@posting.google.c om...

Hi there:

I'm trying to link two large data sets collected from two different
sources for epidemiological research. These two data sets share a few
common fields such as Social Insurance Number, Name (partically),
Gender, etc. Due to incompleteness or errors of some data records, I
guess we have to use probabilistic linkage to find matched records.

Is there any academic/commercial software available for probabilistic
linkage? Or some SQL programming will do it?

Any pointers would be greatly appreciated.

Amy

[DFS]

Amy,

You can use the "Like" keyword, the Left() or Right() functions, or the
InStr() function in the INNER JOIN clause of your SQL. The QBE builder will
be pretty much useless once you add them to the SQL, so you'll have to write
most of it by hand.

Example:

SELECT A.Field1, A.Field2, B.Field1....
FROM Table1 A INNER JOIN Table2 B
ON Left(A.Field1, 5) Like Left(B.Field1, 5)
SELECT A.Field1, A.Field2, B.Field1....
FROM Table1 A INNER JOIN Table2 B
ON InStr(A.Field1, Right(B.Field1,5))


"Amy" <ww**@shaw.ca> wrote in message
news:6c**************************@posting.google.c om...

Hi there:

I'm trying to link two large data sets collected from two different
sources for epidemiological research. These two data sets share a few
common fields such as Social Insurance Number, Name (partically),
Gender, etc. Due to incompleteness or errors of some data records, I
guess we have to use probabilistic linkage to find matched records.

Is there any academic/commercial software available for probabilistic
linkage? Or some SQL programming will do it?

Any pointers would be greatly appreciated.

Amy

[Amy Du]

Gary:

Can you offer any reference on how to estimate the likelihood ratio and
to decide the thresholds? I understand the background theory, but not
sure about practical solutions, i.e., what tools to use.

Thanks a lot!

Amy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pink Panther]

Hi DFS,

I've found it easier to read (via the query builder) if you put the ON
expression in the WHERE clause

eg.
Method A

SELECT A.Field1, A.Field2, B.Field1....
FROM Table1 A INNER JOIN Table2 B
ON Left(A.Field1, 5) Like Left(B.Field1, 5)
becomes
Method B
SELECT A.Field1, A.Field2, B.Field1....
FROM Table1 A, Table2 B
WHERE Left(A.Field1, 5) = Left(B.Field1, 5)

I've tried it both ways and the second method is far less painful as
the Access SQL window ain't much chop IMO (compared to say Query
Analyser or TOAD)...

Just my 2 cents worth on 'non standard' joins - ie not a simple FROM
tbl1 A INNER JOIN tbl2 B ON A.Field1 = B.Field1

Of course LEFT and RIGHT JOINS are more problematic and you'd have to
use method A I presume.

Peter

"DFS" <no****@nospam.com> wrote in message news:<10*************@corp.supernews.com>... Amy,

You can use the "Like" keyword, the Left() or Right() functions, or the
InStr() function in the INNER JOIN clause of your SQL. The QBE builder will
be pretty much useless once you add them to the SQL, so you'll have to write
most of it by hand.

Example:

SELECT A.Field1, A.Field2, B.Field1....
FROM Table1 A INNER JOIN Table2 B
ON Left(A.Field1, 5) Like Left(B.Field1, 5)
SELECT A.Field1, A.Field2, B.Field1....
FROM Table1 A INNER JOIN Table2 B
ON InStr(A.Field1, Right(B.Field1,5))

[Helen Wheels]

Amy wrote:

Hi there:

I'm trying to link two large data sets collected from two different
sources for epidemiological research. These two data sets share a few
common fields such as Social Insurance Number, Name (partically),
Gender, etc. Due to incompleteness or errors of some data records, I
guess we have to use probabilistic linkage to find matched records.

Is there any academic/commercial software available for probabilistic
linkage? Or some SQL programming will do it?

Any pointers would be greatly appreciated.

Amy

Hi Amy
You may find some useful pointers at
http://datamining.anu.edu.au/project...ality_software
Cheers

[AndyM]

LinkageWiz is a great record linkage software program.
The url is http://www.linkagewiz.com

Andrew