How to Merge Datasets (New SPSS User)

[Michael W. Bates]

Help,

I'm a 10 year SAS user learning SPSS.  I know my way around data, but not
around SPSS.  We're running SPSS V15.0 on a Citrix server.

I'm simply trying to do a many to one merge using the GUI in SPSS and
can't seem to get it to work.  The "Merge Files" and "Add Variables"
commands under the "Data" menu will only match the first occurrence of
each value of the field I'm joining on.

I've asked a couple of folks here in the office and they have never done
this with SPSS (and don't seem to think SPSS can do it).  Can SPSS do a
many to one join?  If so, could someone please tell me how?

Also, a code solution will be fine with me.  I'm simply using the GUI to
generate and learn the code.  Left to my own devices, I'm a code person at
heart.

Thanks for any help you can give.

Michael W. Bates
[hidden email]

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

[Bruce Weaver]

Michael W. Bates wrote

Help,

I'm a 10 year SAS user learning SPSS.  I know my way around data, but not
around SPSS.  We're running SPSS V15.0 on a Citrix server.

I'm simply trying to do a many to one merge using the GUI in SPSS and
can't seem to get it to work.  The "Merge Files" and "Add Variables"
commands under the "Data" menu will only match the first occurrence of
each value of the field I'm joining on.

I've asked a couple of folks here in the office and they have never done
this with SPSS (and don't seem to think SPSS can do it).  Can SPSS do a
many to one join?  If so, could someone please tell me how?

Also, a code solution will be fine with me.  I'm simply using the GUI to
generate and learn the code.  Left to my own devices, I'm a code person at
heart.

Thanks for any help you can give.

Michael W. Bates
[e-mail]

Hi Michael.  Welcome to the world of SPSS (or PAWS, or whatever it's called now!).  You may want to bookmark this page:  

   http://www.ats.ucla.edu/stat/Spss/topics/data_management.htm

Take a look at "Merging (MATCH merging) SPSS data files" under Intermediate Data Management.  Pay attention to the distinction between /FILE and /TABLE (in section 3 of the tutorial).

Cheers,
Bruce

[Maguin, Eugene]

Michael,

At the outset, I will say that I actually don't know how to define 'many to
one' (or 'one to many). So, I'll assume you have file X with one record per
value of the matching variable and file Y with multiple records per value of
the matching variable.

In spss language syntax you write

Match files file='Y'/table='X'/by matching_variable.

The way this is talked about in the syntax reference is that a case in X is
'spread across' cases in Y given matching values on the matching variable.

I have no idea if this can done via the menus. Perhaps somebody else will
comment.

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

[Bruce Weaver]

Gene Maguin wrote

Michael,

At the outset, I will say that I actually don't know how to define 'many to
one' (or 'one to many). So, I'll assume you have file X with one record per
value of the matching variable and file Y with multiple records per value of
the matching variable.

In spss language syntax you write

Match files file='Y'/table='X'/by matching_variable.

The way this is talked about in the syntax reference is that a case in X is
'spread across' cases in Y given matching values on the matching variable.

I have no idea if this can done via the menus. Perhaps somebody else will
comment.

Gene Maguin

Gene, in the GUI dialog, /FILE is changed to /TABLE by selecting  "_____ data set is keyed table", where _____ = either "Active" or "Non-active".

[Melissa Ives]

Bruce:
Use this format of a syntax file where the 'file=*' indicates the current working file that is the 'many' side file and the '/TABLE=' gives the location of the 'one' side file and the /BY indicates the matching variable/variables.  Make sure BOTH files are sorted by the matching variable.

MATCH FILES FILE=*
  /TABLE='c:\data\master.sav'
  /BY EMP_ID.

TABLE specifies a table lookup (or keyed table) file. A lookup file contributes variables but not cases to the new active dataset. Variables from the table file are added to all cases from other files that have matching values for the key variables. FILE specifies the files that supply the cases.


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver
Sent: Friday, August 21, 2009 4:30 PM
To: [hidden email]
Subject: Re: [SPSSX-L] How to Merge Datasets (New SPSS User)

Gene Maguin wrote:

>
> Michael,
>
> At the outset, I will say that I actually don't know how to define
> 'many to one' (or 'one to many). So, I'll assume you have file X with
> one record per value of the matching variable and file Y with multiple
> records per value of the matching variable.
>
> In spss language syntax you write
>
> Match files file='Y'/table='X'/by matching_variable.
>
> The way this is talked about in the syntax reference is that a case in
> X is 'spread across' cases in Y given matching values on the matching
> variable.
>
> I have no idea if this can done via the menus. Perhaps somebody else
> will comment.
>
> Gene Maguin
>
>

Gene, in the GUI dialog, /FILE is changed to /TABLE by selecting  "_____ data set is keyed table", where _____ = either "Active" or "Non-active".



-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/
"When all else fails, RTFM."

NOTE:  My hotmail address is for posting only, and messages sent to it will be deleted.

--
View this message in context: http://www.nabble.com/How-to-Merge-Datasets-%28New-SPSS-User%29-tp25087208p25087590.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

[Garry Gelade]

Bruce

SPSS can do many to one join via the menus.  First make sure both files are
sorted in ascending order on the variable(s) you are matching on.

The easiest way is to use the inactive dataset or .sav file as the 'look-up'
table. So make the main file (the one with multiple occurences of the key
variable) your active dataset.  Then select Merge files-->Add variables, and
choose a dataset or .sav file as the look-up table. In the dialog box select
the radio button 'Non-active dataset is keyed table'.  Complete the rest of
the dialog.

Garry Gelade
Business Analytic Ltd

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Bruce Weaver
Sent: 21 August 2009 22:10
To: [hidden email]
Subject: Re: How to Merge Datasets (New SPSS User)

Michael W. Bates wrote:

>
> Help,
>
> I'm a 10 year SAS user learning SPSS.  I know my way around data, but
> not around SPSS.  We're running SPSS V15.0 on a Citrix server.
>
> I'm simply trying to do a many to one merge using the GUI in SPSS and
> can't seem to get it to work.  The "Merge Files" and "Add Variables"
> commands under the "Data" menu will only match the first occurrence of
> each value of the field I'm joining on.
>
> I've asked a couple of folks here in the office and they have never
> done this with SPSS (and don't seem to think SPSS can do it).  Can
> SPSS do a many to one join?  If so, could someone please tell me how?
>
> Also, a code solution will be fine with me.  I'm simply using the GUI
> to generate and learn the code.  Left to my own devices, I'm a code
> person at heart.
>
> Thanks for any help you can give.
>
> Michael W. Bates
> [hidden email]
>

Hi Michael.  Welcome to the world of SPSS (or PAWS, or whatever it's called
now!).  You may want to bookmark this page:

   http://www.ats.ucla.edu/stat/Spss/topics/data_management.htm

Take a look at "Merging (MATCH merging) SPSS data files" under Intermediate
Data Management.  Pay attention to the distinction between /FILE and /TABLE
(in section 3 of the tutorial).

Cheers,
Bruce


-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/
"When all else fails, RTFM."

NOTE:  My hotmail address is for posting only, and messages sent to it will
be deleted.

--
View this message in context:
http://www.nabble.com/How-to-Merge-Datasets-%28New-SPSS-User%29-tp25087208p2
5087362.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

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4357 (20090821) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4357 (20090821) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.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

[Garry Gelade]

Michael

Sorry, I went the message below to Bruce. I should have sent it to you.....

SPSS can do many to one join via the menus.  First make sure both files are
sorted in ascending order on the variable(s) you are matching on.

The easiest way is to use the inactive dataset or .sav file as the 'look-up'
table. So make the main file (the one with multiple occurences of the key
variable) your active dataset.  Then select Merge files-->Add variables, and
choose a dataset or .sav file as the look-up table. In the dialog box select
the radio button 'Non-active dataset is keyed table'.  Complete the rest of
the dialog.

Garry Gelade
Business Analytic Ltd

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Michael W. Bates
Sent: 21 August 2009 20:39
To: [hidden email]
Subject: How to Merge Datasets (New SPSS User)

Help,

I'm a 10 year SAS user learning SPSS.  I know my way around data, but not
around SPSS.  We're running SPSS V15.0 on a Citrix server.

I'm simply trying to do a many to one merge using the GUI in SPSS and can't
seem to get it to work.  The "Merge Files" and "Add Variables"
commands under the "Data" menu will only match the first occurrence of each
value of the field I'm joining on.

I've asked a couple of folks here in the office and they have never done
this with SPSS (and don't seem to think SPSS can do it).  Can SPSS do a many
to one join?  If so, could someone please tell me how?

Also, a code solution will be fine with me.  I'm simply using the GUI to
generate and learn the code.  Left to my own devices, I'm a code person at
heart.

Thanks for any help you can give.

Michael W. Bates
[hidden email]

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

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4357 (20090821) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4357 (20090821) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.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