matching data files

[Paul Mcgeoghan]

I have 2 files:
One has IP addresses in a column such as:
123.456.78.0
123.456.123.12

The other file has 2 columns IPStart and IPEnd and other columns such as
Location
IPStart       IPEnd            Location
123.456.77.1  123.456.78.99    A
123.456.122.0 123.456.123.50   B

I want to match the 2 files so that I end up with
IP               Location
123.456.78.0       A
123.456.123.12     B

The first 2 parts of the IP address range do not change, the last 2 parts
can be anything.

Thanks.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi,

Untested, but this should work. If you create a variable newip in each file, you should be able to match the two files.
string newip (a11).
compute newip = substr(ip, 1, rindex(ip, ".")-1).
Cheers|!
Albert-Jan

--- On Mon, 6/1/09, Paul McGeoghan <[hidden email]> wrote:

> From: Paul McGeoghan <[hidden email]>
> Subject: matching data files
> To: [hidden email]
> Date: Monday, June 1, 2009, 11:37 AM
> I have 2 files:
> One has IP addresses in a column such as:
> 123.456.78.0
> 123.456.123.12
>
> The other file has 2 columns IPStart and IPEnd and other
> columns such as
> Location
> IPStart       IPEnd
>         Location
> 123.456.77.1  123.456.78.99    A
> 123.456.122.0 123.456.123.50   B
>
> I want to match the 2 files so that I end up with
> IP
>    Location
> 123.456.78.0       A
> 123.456.123.12     B
>
> The first 2 parts of the IP address range do not change,
> the last 2 parts
> can be anything.
>
> Thanks.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email]
> (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the
> command
> INFO REFCARD
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Paul Mcgeoghan]

When I said
'The first 2 parts of the IP address range do not change, the last 2 parts
can be anything.'
I meant within the range: IPstart and IPEnd, they do not change.

So I want to compare the IP address in File 1 to the IP address ranges in
File 2 where there are many ranges stored in IPstart and IPend.
When the IP address in File 1 occurs in the relevant IP range in File 2,
extract the data from the location field in File 2.

Hope that makes it clearer.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Maguin, Eugene]

Paul,

So what you want to do is a match file with file 1 as the table and where
the by variable is the address range. Is that a correct summary?

Since you're reposting after somebody offered a method, can I assume that
their solution wasn't what you needed?

Gene Maguin



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Paul McGeoghan
Sent: Monday, June 01, 2009 7:20 AM
To: [hidden email]
Subject: Re: matching data files

When I said
'The first 2 parts of the IP address range do not change, the last 2 parts
can be anything.'
I meant within the range: IPstart and IPEnd, they do not change.

So I want to compare the IP address in File 1 to the IP address ranges in
File 2 where there are many ranges stored in IPstart and IPend.
When the IP address in File 1 occurs in the relevant IP range in File 2,
extract the data from the location field in File 2.

Hope that makes it clearer.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[djhurio]

Just to give you an idea. There is an example. It is not perfect. Sorting by IP address is tricky. Sorting would work if IP addresses would be with 12 digits including leading "0" - for example 123.456.078.000

cd "C:\TEMP\SPSS".

***.

data list free
 /IP (a16).
begin data
123.456.78.0
123.456.123.12
end data.

save out "data_IP.sav".

***.

data list free
 /IPStart (a16) IPEnd (a16) Location (a1).
begin data
123.456.77.1  123.456.78.99    A
123.456.122.0 123.456.123.50   B
end data.

save out "data_Loc.sav".

***.

add files
 /file "data_Loc.sav"
 /ren IPStart=IP
 /in start
 /file "data_Loc.sav"
 /ren IPEnd=IP
 /in end
 /file "data_IP.sav"
 /in data
 /drop IPEND IPStart.

sort cases IP.

if data Location=lag(Location).

exe.

sel if data.

exe.

Paul Mcgeoghan wrote

I have 2 files:
One has IP addresses in a column such as:
123.456.78.0
123.456.123.12

The other file has 2 columns IPStart and IPEnd and other columns such as
Location
IPStart       IPEnd            Location
123.456.77.1  123.456.78.99    A
123.456.122.0 123.456.123.50   B

I want to match the 2 files so that I end up with
IP               Location
123.456.78.0       A
123.456.123.12     B

The first 2 parts of the IP address range do not change, the last 2 parts
can be anything.

Thanks.

=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Paul Mcgeoghan]

Gene,

Yes, that is correct.

The first solution didn't solve my problem from what I can see.
(Thanks for the advice though Albert-Jan)

Paul

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Maguin, Eugene]

Paul,

I don't have your original posting and I don't recall if you said anything
about this. Your IP address data are actually text strings, perhaps A15, and
not numbers. Is this true?

How big are your two files, how many records?

I'm just pretty sure that this problem has come up before but not in the
recent past. Perhaps in the last two or three years. Have you looked at Ray
Levesque's book, which should be in your installation directory?

If file A, I think you called it, the file that is to be the table file,
weren't too big AND the IP addresses were numbers, not text strings, you
could do save just the IPAddress file to a new file, and then do a
casestovars on that new file to create a file with just one record. That
one-record file is the table file to the second file, File B, (there's also
a tiny trick to make the match files work, but that is beside the point just
now). So now every record of file B has acquired many new variables. So you
put those new variables in a vector and test the elements of the vectors
against the range endpoints. Blank ($sysmis) out the ones that are not
within range and keep the rest (at most one, you hope). So the main problem
that can come up is if multiple elements of the vector are within the range
endpoints. So that's not too hard, I think.

But you have strings, I'll bet. And that file A could also be huge.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 02:17 PM 6/1/2009, Gene Maguin wrote:

>I'm just pretty sure that this problem has come up before

The problem of matching a key to a range does come up from time to
time, most commonly where the keys are dates. I've posted on it, but
I can't find where, now.

I think djhurio's approach is essentially the right one, including
addressing the question of building a sortable form of the IP
addresses. Have you got it to work, or do you still have questions?

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[djhurio]

OK, here is a complete solution.

I am assuming that IP ranges are not overlapping.
IP2 is a sortable IP address.
IPRangeID is 0 for IPs out of any range defined and 1,2,3... for other IPs in ranges.

HTH.

P.S. I have used many EXECUTE commands because of the lag() functions.



cd "C:\TEMP\SPSS".

***.

data list free
 /IP (a15).
begin data
1.2.3.4
123.456.78.0
123.456.78.2
123.456.100.4
123.456.123.12
999.7.7.7
end data.

save out "data_IP.sav".

***.

data list free
 /IPStart (a15) IPEnd (a15) Location (a1).
begin data
123.456.77.1  123.456.78.99    A
123.456.122.0 123.456.123.50   B
end data.

save out "data_Loc.sav".

***.

add files
 /file "data_Loc.sav"
 /ren IPStart=IP
 /in start
 /file "data_Loc.sav"
 /ren IPEnd=IP
 /in end
 /file "data_IP.sav"
 /in data
 /drop IPEND IPStart.

string IP2 (a15).
string #temp (a12).

comp IP2=str(num(sub(IP,1,index(IP,".")-1),f3),n3).
comp #temp=con(sub(IP,index(IP,".")+1),".").

loop #k=1 to 3.
comp IP2=con(rtr(IP2),".",str(num(sub(#temp,1,index(#temp,".")-1),f3),n3)).
comp #temp=sub(#temp,index(#temp,".")+1).
end loop.

sort cases IP2.

do if start.
comp #counter=sum(lag(#counter),1).
else.
comp #counter=sum(lag(#counter),0).
end if.

do if start.
comp IPRangeID=#counter.
else if ~start and (lag(end) or $casenum=1).
comp IPRangeID=0.
else.
comp IPRangeID=lag(IPRangeID).
end if.

exe.

if data and IPRangeID>0 Location=lag(Location).

exe.

string IPStart IPEnd (a15).

do if start.
comp IPStart=IP.
else if IPRangeID>0.
comp IPStart=lag(IPStart).
else.
comp IPStart="".
end if.

exe.

sort cases IP2 (d).

do if end.
comp IPEnd=IP.
else if IPRangeID>0.
comp IPEnd=lag(IPEnd).
else.
comp IPEnd="".
end if.

exe.

sort cases IP2.

sel if data.

exe.

del var start end data IP2.

Richard Ristow wrote

At 02:17 PM 6/1/2009, Gene Maguin wrote:

>I'm just pretty sure that this problem has come up before

The problem of matching a key to a range does come up from time to
time, most commonly where the keys are dates. I've posted on it, but
I can't find where, now.

I think djhurio's approach is essentially the right one, including
addressing the question of building a sortable form of the IP
addresses. Have you got it to work, or do you still have questions?

=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Bibel, Daniel (POL)]

I have a file with about 50,000 records of individuals.  Records may
have been entered by different agencies at different times, and of
course there are misspellings, transpositions of numbers (in date of
birth, ss#, etc.)

I would like to be able to do some sort of 'fuzzy matching' to find
potential duplicate cases where there may be some potential mistakes in
data entry for name and dob.  'Name' is a string field, and 'DOB' is
also an 8 character string field in the format of 'YYYYMMDD'.

Thanks for any help and suggestions.

Daniel Bibel
Massachusetts State Police
Crime Reporting Unit

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Anton-24]

Hi Daniel-

I've used Link Plus, which is free from the CDC.  It's easy to learn and
does a fairly good job of identifying potential duplicates, however, it
works better if you have more than 2 variables.

http://www.cdc.gov/cancer/npcr/tools/registryplus/lp.htm

Anton



On Tue, 2 Jun 2009 09:58:16 -0400, Bibel, Daniel <[hidden email]>
wrote:

>I have a file with about 50,000 records of individuals.  Records may
>have been entered by different agencies at different times, and of
>course there are misspellings, transpositions of numbers (in date of
>birth, ss#, etc.)
>
>I would like to be able to do some sort of 'fuzzy matching' to find
>potential duplicate cases where there may be some potential mistakes in
>data entry for name and dob.  'Name' is a string field, and 'DOB' is
>also an 8 character string field in the format of 'YYYYMMDD'.
>
>Thanks for any help and suggestions.
>
>Daniel Bibel
>Massachusetts State Police
>Crime Reporting Unit
>
>=====================
>To manage your subscription to SPSSX-L, send a message to
>[hidden email] (not to SPSSX-L), with no body text except the
>command. To leave the list, send the command
>SIGNOFF SPSSX-L
>For a list of commands to manage subscriptions, send the command
>INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Peck, Jon]

There are various specialized packages that deal with name discrepancies and other such records noise.  While the FUZZY extension command can match data sources with some fuzz, the fuzz works only for numeric variables.
However, there are two functions available via programmability that may help with name mismatches.  The soundex and nysiis functions in the extendedTransforms.py module can code names in such a way that spelling variation differences are minimized.
These can be downloaded from SPSS Developer Central (www.spss.com/devcentral).
Converting the date to a regular SPSS date variable might be useful in finding gross errors (the Data Validation option can help here).
A full solution, though, requires specialized software unless you want to spend a lot of time coding rules.
HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bibel, Daniel
Sent: Tuesday, June 02, 2009 7:58 AM
To: [hidden email]
Subject: [SPSSX-L] question on 'fuzzy matching'

I have a file with about 50,000 records of individuals.  Records may
have been entered by different agencies at different times, and of
course there are misspellings, transpositions of numbers (in date of
birth, ss#, etc.)

I would like to be able to do some sort of 'fuzzy matching' to find
potential duplicate cases where there may be some potential mistakes in
data entry for name and dob.  'Name' is a string field, and 'DOB' is
also an 8 character string field in the format of 'YYYYMMDD'.

Thanks for any help and suggestions.

Daniel Bibel
Massachusetts State Police
Crime Reporting Unit

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi,

I used FEBRL (freeware) to do fuzzy matching, it includes routines for standardization and cleaning data and a lot of comparison functions, e.g. for dob fields, nysiis, (reverse) soundex, etc etc. It's written in Python, but the latest version has a menu (GUI).

Cheers!!
Albert-Jan


--- On Tue, 6/2/09, Peck, Jon <[hidden email]> wrote:

> From: Peck, Jon <[hidden email]>
> Subject: Re: question on 'fuzzy matching'
> To: [hidden email]
> Date: Tuesday, June 2, 2009, 4:52 PM
> There are various specialized
> packages that deal with name discrepancies and other such
> records noise.  While the FUZZY extension command can
> match data sources with some fuzz, the fuzz works only for
> numeric variables.
> However, there are two functions available via
> programmability that may help with name mismatches.
> The soundex and nysiis functions in the
> extendedTransforms.py module can code names in such a way
> that spelling variation differences are minimized.
> These can be downloaded from SPSS Developer Central
> (www.spss.com/devcentral).
> Converting the date to a regular SPSS date variable might
> be useful in finding gross errors (the Data Validation
> option can help here).
> A full solution, though, requires specialized software
> unless you want to spend a lot of time coding rules.
> HTH,
> Jon Peck
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]]
> On Behalf Of Bibel, Daniel
> Sent: Tuesday, June 02, 2009 7:58 AM
> To: [hidden email]
> Subject: [SPSSX-L] question on 'fuzzy matching'
>
> I have a file with about 50,000 records of
> individuals.  Records may
> have been entered by different agencies at different times,
> and of
> course there are misspellings, transpositions of numbers
> (in date of
> birth, ss#, etc.)
>
> I would like to be able to do some sort of 'fuzzy matching'
> to find
> potential duplicate cases where there may be some potential
> mistakes in
> data entry for name and dob.  'Name' is a string
> field, and 'DOB' is
> also an 8 character string field in the format of
> 'YYYYMMDD'.
>
> Thanks for any help and suggestions.
>
> Daniel Bibel
> Massachusetts State Police
> Crime Reporting Unit
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email]
> (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the
> command
> INFO REFCARD
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email]
> (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the
> command
> INFO REFCARD
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Paul Mcgeoghan]

Martins,

Your first post seemed to already do the trick. Just got around to trying
it.

add files
 /file "data_Loc.sav"
 /ren IPStart=IP
 /in start
 /file "data_Loc.sav"
 /ren IPEnd=IP
 /in end
 /file "data_IP.sav"
 /in data
 /drop IPEND IPStart.

sort cases IP.

if data Location=lag(Location).

exe.

sel if data.

exe.


In my 2nd data file (the IP range file), as well as Location I have
IPStart, IPEnd, Location, Dept, Core, Description.

What do I need to modify to add the extra columns?

Do I need to use the 2nd syntax you sent as the first appears to work?

Thanks
Paul

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 04:00 AM 6/2/2009, djhurio wrote:

OK, here is a complete solution.

It certainly seems to work for me.

I am assuming that IP ranges are not overlapping.

Right. If you had overlapping ranges, the correct answer would probably be an error message, since I would say the required solution is not defined in this case.

IPRangeID is 0 for IPs out of any range defined and 1,2,3... for other IPs
in ranges.

A nice touch.

P.S. I have used many EXECUTE commands because of the lag() functions.

It works fine without them, and I think it's noticeably faster, even on this small dataset. Some logic using LAG requires EXECUTE to work properly; but most, by far, does not(*). In your code, the one useful EXECUTE is the last one, which clears pending transformations so DELETE VARIABLES will run.

There's a listing appended, of your code with some re-formatting, unneeded EXECUTES removed, and using datasets (SPSS 14 or later) instead of scratch disk files. You could avoid the SORT CASES by keeping IPend initially, and carrying it forward from the 'start' record that begins a range.

There's a glitch where it raises an error converting the fourth part of IP address "1.2.3.4" to three digits. It happens only with that address. I haven't tried to figure it out.

............................
(*) See "Lag Functions" under "Use EXECUTE Sparingly", in Part I chapter 2 of any edition of Levesque et al, Programming and Data Management for SPSS®: A Guide for and SAS® Users. The most recent edition is Levesque, Raynald, and SPSS Inc., Programming and Data Management for SPSS® Statistics 17.0: A Guide for SPSS Statistics and SAS® Users. Chicago, IL; SPSS Inc. (date not given).

==================================================
APPENDIX:  Test data, and code (as output listing)
==================================================
*  From the posting responded to, changing                           .
*  A.  Expand command names to full names, e.g. "comp" -> "COMPUTE"  .
*      Expand function names, ditto,       e.g. "con(" -> "CONCAT("  .
*  B.  In the same spirit, indent lines that are within constructs   .
*  C.  Datasets instead of scratch files:                            .
*      "data_IP.sav"   --> dataset "data_IP"                         .
*      "data_Loc.sav"  --> dataset "data_Loc"                        .
*      Drop "CD" statement.                                          .
*  D.  Add "LIST" at the end                                         .
*                                                                    .
*  E.  Comment out the ".exe" statements                             .

*  xxx   cd "C:\TEMP\SPSS"   xxx.

***.

data list free
 /IP (a15).
begin data
1.2.3.4
123.456.78.0
123.456.78.2
123.456.100.4
123.456.123.12
999.7.7.7
end data.

*  xxx save out     "data_IP.sav"   xxx.
.      DATASET NAME  data_IP.
***.

data list free
 /IPStart (a15) IPEnd (a15) Location (a1).
begin data
123.456.77.1  123.456.78.99    A
123.456.122.0 123.456.123.50   B
end data.

*  xxx save out    "data_Loc.sav"   xxx.
.      DATASET NAME data_Loc.
***.

add files
 /file data_Loc
 /RENAME IPStart=IP
 /IN = start
 /file data_Loc
 /RENAME IPEnd=IP
 /IN = end
 /file data_IP
 /IN = data
 /drop IPEND IPStart.

string IP2 (a15).
string #temp (a12).

COMPUTE  IP2=STRING(NUMBER(SUBSTR(IP,1,index(IP,".")-1),f3),n3).
COMPUTE  #temp=CONCAT(SUBSTR(IP,index(IP,".")+1),".").

loop #k=1 to 3.
.  COMPUTE  IP2=CONCAT(RTRIM(IP2),".",
                       STRING(NUMBER(SUBSTR(#temp,1,index(#temp,".")-1),f3),n3))
   .
.  COMPUTE #temp=SUBSTR(#temp,index(#temp,".")+1).
end loop.

.  /*-- LIST /*-*/.

sort cases IP2.
>Warning # 606
>The third argument to SUBSTR (the length) is missing or is otherwise
>invalid.  The argument must be a non-negative integer.  The result has been
>set to the null string.

>Command line: 124  Current case: 5  Current splitfile group: 1


>Warning # 635
>The string to be converted via the NUMBER function is of zero length.  The
>result has been set to the system-missing value.

>Command line: 124  Current case: 5  Current splitfile group: 1


do if start.
.  COMPUTE #counter=sum(lag(#counter),1).
else.
.  COMPUTE #counter=sum(lag(#counter),0).
end if.

do if start.
.  COMPUTE IPRangeID=#counter.
else if ~start and (lag(end) or $casenum=1).
.  COMPUTE IPRangeID=0.
else.
.  COMPUTE IPRangeID=lag(IPRangeID).
end if.

.  /*--  EXECUTE /*-*/.

if data and IPRangeID>0 Location=lag(Location).

.  /*--  EXECUTE /*-*/.

string IPStart IPEnd (a15).

do if start.
.  COMPUTE IPStart=IP.
else if IPRangeID>0.
.  COMPUTE IPStart=lag(IPStart).
else.
.  COMPUTE IPStart="".
end if.

.  /*--  LIST    /*-*/.
.  /*--  EXECUTE /*-*/.

sort cases IP2 (d).
do if end.
.  COMPUTE IPEnd=IP.
else if IPRangeID>0.
.  COMPUTE IPEnd=lag(IPEnd).
else.
.  COMPUTE IPEnd="".
end if.

.  /*--  EXECUTE /*-*/.

SORT CASES IP2.
SELECT IF data.

.  /*--  EXECUTE /*-*/.

EXECUTE /* in order to make "DELETE VARIABLES" available */.
DELETE VARIABLES start end data IP2.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |02-JUN-2009 15:04:14       |
|-----------------------------|---------------------------|
IP              Location IPRangeID IPStart         IPEnd

1.2.3.4                        .00
123.456.78.0    A             1.00 123.456.77.1    123.456.78.99
123.456.78.2    A             1.00 123.456.77.1    123.456.78.99
123.456.100.4                  .00
123.456.123.12  B             2.00 123.456.122.0   123.456.123.50
999.7.7.7                      .00

Number of cases read:  6    Number of cases listed:  6
====================To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[djhurio]

Paul,

If the first solution is fine, it is easy to keep extra variables. I have modified it by making it more simple.

***.

cd "C:\TEMP\SPSS".

***.

data list free
 /IP (a15).
begin data
123.456.78.0
123.456.123.12
end data.

save out "data_IP.sav".

***.

data list free
 /IPStart (a15) IPEnd (a15) Location (a1).
begin data
123.456.77.1  123.456.78.99    A
123.456.122.0 123.456.123.50   B
end data.

string IP (a15).
comp IP=IPStart.

save out "data_Loc.sav".

***.

add files
 /file "data_Loc.sav"
 /file "data_IP.sav"
 /in data.

sort cases IP.

*** Here you can define more variable to keep ***.
do rep var=IPStart IPEnd Location.
if data var=lag(var).
end rep.

exe.

sel if data.

exe.

del var data.

list.


Richard,

I do not get the warning you are getting. Strange it is saying "The third argument to SUBSTR (the length) is missing ...". The third argument of SUBSTR is optional as far as I remember in all versions. I am using SPSS 17.0.0.

Thanks for the advises!

I just hate datasets or multiple data windows in SPSS. That is why I am using files :)


Martins

[Dennis Deck]

Also consider LinkPlus, free from the CDC web site.
http://www.cdc.gov/cancer/npcr/tools/registryplus/lp.htm
It is a stand alone program and is easy to use.

Linkage programs use various strategies to find the degree of match
between a pair of records. They tend to do a good job with the types of
problems you mention as long as there are several variables to match on.
You do have to decide the relative risks of under or over identification
and figure out where to draw the line.

Note that in my experience police records are particularly problematic,
however.  Identity theft issues are hard to detect.

Dennis Deck
RMC Research

-----Original Message-----
From: Bibel, Daniel [mailto:[hidden email]]
Sent: Tuesday, June 02, 2009 6:58 AM
Subject: question on 'fuzzy matching'

I have a file with about 50,000 records of individuals.  Records may
have been entered by different agencies at different times, and of
course there are misspellings, transpositions of numbers (in date of
birth, ss#, etc.)

I would like to be able to do some sort of 'fuzzy matching' to find
potential duplicate cases where there may be some potential mistakes in
data entry for name and dob.  'Name' is a string field, and 'DOB' is
also an 8 character string field in the format of 'YYYYMMDD'.

Thanks for any help and suggestions.

Daniel Bibel
Massachusetts State Police
Crime Reporting Unit

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD