Match files mystery

[Raffe, Sydelle, SSA]

I'm running the following syntax to match 2 files and I get the error message about a duplicate key in file 2, my table file.

I've used 'identify duplicates', and sorted the cases backwards and forwards to try and find the duplicate key it detects. But, I can't find any problem

Anyone have a suggestion?  Thanks.

GET
  FILE='M:\Monthly Data Files\BHCS\Bhcs07-08\03-08\bhcs_nr_03-08_person shrt.sav'.
MATCH FILES /FILE=*
 /table='M:\Monthly Data Files\BHCS\BHCS07-08\03-08\1 thru 15 cwin demos no dupes04-16-08.sav'
 /BY cwin.
EXECUTE.
File #2
     KEY:        .

>Error # 5131
>Duplicate key on a TABLE file.  Each case on a TABLE file in MATCH FILES
>must be uniquely identified by the BY variables.
>This command not executed.


Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form at:  https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

====================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

[David Wasserman]

It appears that you have more than one record in the TABLE file with missing
values for CWIN, your match variable, which is apparently a numeric
variable.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Raffe, Sydelle, SSA
Sent: April-17-08 12:03 PM
To: [hidden email]
Subject: Match files mystery

I'm running the following syntax to match 2 files and I get the error
message about a duplicate key in file 2, my table file.

I've used 'identify duplicates', and sorted the cases backwards and forwards
to try and find the duplicate key it detects. But, I can't find any problem

Anyone have a suggestion?  Thanks.

GET
  FILE='M:\Monthly Data Files\BHCS\Bhcs07-08\03-08\bhcs_nr_03-08_person
shrt.sav'.
MATCH FILES /FILE=*
 /table='M:\Monthly Data Files\BHCS\BHCS07-08\03-08\1 thru 15 cwin demos no
dupes04-16-08.sav'
 /BY cwin.
EXECUTE.
File #2
     KEY:        .

>Error # 5131
>Duplicate key on a TABLE file.  Each case on a TABLE file in MATCH FILES
>must be uniquely identified by the BY variables.
>This command not executed.


Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form at:
https://alamedasocialservices.org/staff/support_services/statistics_and_repo
rts/odm/index.cfm

=======
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

[Raffe, Sydelle, SSA]

Thanks for all your responses. I'm convinced SPSS was detecting phantoms. I went back to my original file, manually deleted those records with sysmis and all was fine.

Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form at:  https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

====================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

[Oliver, Richard]

If you ran Identify Duplicate Cases on the table-lookup file, it should have identified all the cases with sysmis for the ID variable as duplicates.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Raffe, Sydelle, SSA
Sent: Thursday, April 17, 2008 2:24 PM
To: [hidden email]
Subject: Re: match files mystery

Thanks for all your responses. I'm convinced SPSS was detecting phantoms. I went back to my original file, manually deleted those records with sysmis and all was fine.

Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form at:  https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

=======
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

[Art Kendall]

open the *table* file. then try something like this untested syntax.
numeric dup (comma10).
sort cases by cwin
do if $casenum ne 1.
if cwin eq lag(cwin)  dup = $casenum.
end if.
frequencies vars=dup.

Art
Raffe, Sydelle, SSA wrote:

> I did but spss shut down on me -- perhaps because the file is over
> 500,000? I'm going to try again.
>
>     -----Original Message-----
>     *From:* Art Kendall [mailto:[hidden email]]
>     *Sent:* Thursday, April 17, 2008 11:58 AM
>     *To:* Raffe, Sydelle, SSA
>     *Subject:* Re: Match files mystery
>
>     try a FREQUENCIES on cwin in the table file.
>
>     Art Kendall
>     Social Research Consultants
>
>     Raffe, Sydelle, SSA wrote:
>>     I'm running the following syntax to match 2 files and I get the error message about a duplicate key in file 2, my table file.
>>
>>     I've used 'identify duplicates', and sorted the cases backwards and forwards to try and find the duplicate key it detects. But, I can't find any problem
>>
>>     Anyone have a suggestion?  Thanks.
>>
>>     GET
>>       FILE='M:\Monthly Data Files\BHCS\Bhcs07-08\03-08\bhcs_nr_03-08_person shrt.sav'.
>>     MATCH FILES /FILE=*
>>      /table='M:\Monthly Data Files\BHCS\BHCS07-08\03-08\1 thru 15 cwin demos no dupes04-16-08.sav'
>>      /BY cwin.
>>     EXECUTE.
>>     File #2
>>          KEY:        .
>>
>>
>>>     Error # 5131
>>>     Duplicate key on a TABLE file.  Each case on a TABLE file in MATCH FILES
>>>     must be uniquely identified by the BY variables.
>>>     This command not executed.
>>>
>>
>>
>>     Sydelle Raffe, Alameda County Social Services Agency
>>     Information Services Division, Office of Data Management
>>     e:mail:  [hidden email]
>>     phone: [phone]     fax: [phone]
>>     If you have a request for information, please submit an ODM request form at:  https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm
>>
>>     ===================
>>     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

[Dennis Deck]

* NOTES:
  Here is some simple code for identifying and eliminating duplicate
records
  David thought ID might be missing - that may be your first problem
  Substitute name of your key variable for ID
  This code is untested   .

*** Define ID if Missing *** .

IF MISSING(ID) ID = 100000 + $Casenum .

* Note: Only needed if ID is sometimes missing
        You may need to adjust this but idea is to create
        a unique value outside the range of existing key values .

*** Find a duplicate (no missing data in ID) *** .

SORT CASES BY ID  .

* identify the duplicates .
COMPUTE RecNo = 1 .
IF ($casenum>1 and ID=LAG(ID)) RecNo = LAG(RecNo) + 1 .

FREQUENCIES Vars= RecNo .

* remove the duplicates .
SELECT IF  (RecNo=1) .


-----Original Message-----
From: David Wasserman [mailto:[hidden email]]
Sent: Thursday, April 17, 2008 11:46 AM
Subject: Re: Match files mystery

It appears that you have more than one record in the TABLE file with
missing values for CWIN, your match variable, which is apparently a
numeric variable.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Raffe, Sydelle, SSA
Sent: April-17-08 12:03 PM
To: [hidden email]
Subject: Match files mystery

I'm running the following syntax to match 2 files and I get the error
message about a duplicate key in file 2, my table file.

I've used 'identify duplicates', and sorted the cases backwards and
forwards to try and find the duplicate key it detects. But, I can't find
any problem

Anyone have a suggestion?  Thanks.

GET
  FILE='M:\Monthly Data Files\BHCS\Bhcs07-08\03-08\bhcs_nr_03-08_person
shrt.sav'.
MATCH FILES /FILE=*
 /table='M:\Monthly Data Files\BHCS\BHCS07-08\03-08\1 thru 15 cwin demos
no dupes04-16-08.sav'
 /BY cwin.
EXECUTE.
File #2
     KEY:        .

>Error # 5131
>Duplicate key on a TABLE file.  Each case on a TABLE file in MATCH
>FILES must be uniquely identified by the BY variables.
>This command not executed.


Sydelle Raffe, Alameda County Social Services Agency Information
Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form
at:
https://alamedasocialservices.org/staff/support_services/statistics_and_
repo
rts/odm/index.cfm

=======
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