Many-to-One Issue

[cfishernyc]

Hi All -

I'm familiar with merging many to one, and even refreshed myself by going over the archives. For some reason, it's not working with my current data.

I have two files with one common key. In one file the common key (RIN) has duplicates (within a client) as the clients have a row for each admission. In the other, the RIN only occurs once, as they have one DOB.
For some reason, no matter if I make the current table keyed or non-keyed, it freaks out. If it actually allows the match, it mismatches people, and puts a clients DOB in multiple rows, but not just the rows belonging to that client. I've sorted. I've checked for accidental duplicates. I'm stumped. Does anyone have any suggestions?

File1

RIN      Name         DOB
1234    bobsmith    3/3/03
1235    sarahtall     4/5/02
.....

File2
RIN       Name        AdmissionDate    
1234     bobsmith   3/3/10
1234     bobsmith   4/4/05
1234     bobsmith   5/5/06
1235     sarahtall ......

[Melissa Ives]

Seems to me that this should work--you could also match by name, but text field matches can be challenging unless you are positive that there are no spelling, spacing, capitalization differences.
The table file is the one with only one record for each BY variable.
The /IN command will create a variable (inF1) that is 0/1 indicator of if there was a matching record in File1.

Sort cases by RIN.
Match files file=File2/
        /table=File1/in=inF1
        /by RIN.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of cfishernyc
Sent: Wednesday, September 17, 2008 5:44 PM
To: [hidden email]
Subject: [SPSSX-L] Many-to-One Issue

Hi All -

I'm familiar with merging many to one, and even refreshed myself by going over the archives. For some reason, it's not working with my current data.

I have two files with one common key. In one file the common key (RIN) has duplicates (within a client) as the clients have a row for each admission.
In the other, the RIN only occurs once, as they have one DOB.
For some reason, no matter if I make the current table keyed or non-keyed, it freaks out. If it actually allows the match, it mismatches people, and puts a clients DOB in multiple rows, but not just the rows belonging to that client. I've sorted. I've checked for accidental duplicates. I'm stumped.
Does anyone have any suggestions?

File1

RIN      Name         DOB
1234    bobsmith    3/3/03
1235    sarahtall     4/5/02
.....

File2
RIN       Name        AdmissionDate
1234     bobsmith   3/3/10
1234     bobsmith   4/4/05
1234     bobsmith   5/5/06
1235     sarahtall ......
--
View this message in context: http://www.nabble.com/Many-to-One-Issue-tp19542188p19542188.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

=====================
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 06:44 PM 9/17/2008, cfishernyc wrote:

>I have two files with one common key. In one file the common key
>(RIN) has duplicates (within a client) as the clients have a row for
>each admission. In the other, the RIN only occurs once, as they have one DOB.
>For some reason, no matter if I make the current table keyed or
>non-keyed, it freaks out.

What happens when you tried it? Anyhow, Melissa's suggestion should
be fine. Here's a demo, with your test data and the logic Melissa
suggested. Notice that RIN is used for matching, without Name.

DATASET ACTIVATE File1 WINDOW=FRONT.
LIST.
List
|-----------------------------|---------------------------|
|Output Created               |18-SEP-2008 23:35:34       |
|-----------------------------|---------------------------|
[File1]
  RIN Name              DOB

1234 bobsmith   03/03/2003
1235 sarahtall  04/05/2002

Number of cases read:  2    Number of cases listed:  2


DATASET ACTIVATE File2 WINDOW=FRONT.
LIST.
List
|-----------------------------|---------------------------|
|Output Created               |18-SEP-2008 23:35:34       |
|-----------------------------|---------------------------|
[File2]
  RIN Name_2     AdmissionDate

1234 bobsmith     03/03/2010
1234 bobsmith     04/04/2005
1234 bobsmith     05/05/2006
1235 sarahtall    07/07/2008

Number of cases read:  4    Number of cases listed:  4


MATCH FILES
    /TABLE=File1
    /FILE =File2
    /BY RIN.

LIST.
List
|-----------------------------|---------------------------|
|Output Created               |18-SEP-2008 23:35:34       |
|-----------------------------|---------------------------|
  RIN Name              DOB Name_2     AdmissionDate

1234 bobsmith   03/03/2003 bobsmith     03/03/2010
1234 bobsmith   03/03/2003 bobsmith     04/04/2005
1234 bobsmith   03/03/2003 bobsmith     05/05/2006
1235 sarahtall  04/05/2002 sarahtall    07/07/2008

Number of cases read:  4    Number of cases listed:  4
=============================
APPENDIX: Test data, and code
=============================
*   File1.
DATA LIST LIST /
     RIN      Name         DOB
    (F4,     A10,        ADATE10).
BEGIN DATA
     1234    bobsmith    3/3/03
     1235    sarahtall     4/5/02
END DATA.
DATASET NAME     File1 WINDOOW=FRONT.

*   File2.
DATA LIST LIST /
     RIN       Name_2    AdmissionDate
    (F4,     A10,        ADATE10).
BEGIN DATA
     1234     bobsmith   3/3/10
     1234     bobsmith   4/4/05
     1234     bobsmith   5/5/06
     1235     sarahtall  7/7/08
END DATA.
DATASET NAME     File2 WINDOW=FRONT.

DATASET ACTIVATE File1 WINDOW=FRONT.
LIST.
DATASET ACTIVATE File2 WINDOW=FRONT.
LIST.

MATCH FILES
    /TABLE=File1
    /FILE =File2
    /BY RIN.
LIST.

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