A 'many to one' merge in SPSS

[SabatoPsy]

Hello everyone:

I am working with a multilevel dataset (days (level 1) nested within people (level 2)) and have created some mean daily variables that I want to run analyses with.  I want to use these variables in a regular (non MIXED) regression analyses in predicting certain level 2 trait outcomes.  

I created them in a dataset I created by doing a 'one to many' merge of my level 1 and level 2 datasets such that each day is a case (by using the /TABLE command rather than /FILE command with MATCHED FILES).  While I could do my regular regressions in this dataset, my N is going to deflate my standard errors since my "sample size" is the number of days rather than the number of people in the dataset.  Since I am regressing a level 2 trait outcome onto an aggregated level 1 variable (aka pseudo-level 2), I want my sample size to be the number of people in the study (not number of days).

Does anyone know how I can merge in variables that are the same value across my keyed variable (i.e. person ID), but repeated multiple times for each day (aka case), in a level 1 dataset, to a level 2 dataset where there is only one case for each keyed variable (i.e. person ID)?  When I try to do a regular merge the new dataset has each day as a case, rather than each person.

Much appreciated.  I am sure others have had similar struggles.

David Disabato
George Mason University

[Andy W]

I suspect your overthinking it. If I understand correctly, you should just eliminate duplicates in the "person*day" dataset (where the unique id is the person), and then just merge those remaining cases to the "person" dataset.

[Maguin, Eugene]

I'm not quite sure I understand what you want I think I'd express it this way. You aggregated some level 1 variables by person to make a dataset and you could match that file to your person file. So that would let you do person-level regressions using person level variables and aggregated day variables.

Now this kind of seems like what you want but I'm not sure why. You could do a casestovars on your level 2-level 1 file (days within persons) to get a file with your person variables and as many sets of day variables as a person has days on each record. A so-called wide/multivariate format file.
Gene Maguin


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of SabatoPsy
Sent: Friday, August 16, 2013 1:41 AM
To: [hidden email]
Subject: A 'many to one' merge in SPSS

Hello everyone:

I am working with a multilevel dataset (days (level 1) nested within people (level 2)) and have created some mean daily variables that I want to run analyses with.  I want to use these variables in a regular (non MIXED) regression analyses in predicting certain level 2 trait outcomes.

I created them in a dataset I created by doing a 'one to many' merge of my level 1 and level 2 datasets such that each day is a case (by using the /TABLE command rather than /FILE command with MATCHED FILES).  While I could do my regular regressions in this dataset, my N is going to deflate my standard errors since my "sample size" is the number of days rather than the number of people in the dataset.  Since I am regressing a level 2 trait outcome onto an aggregated level 1 variable (aka pseudo-level 2), I want my sample size to be the number of people in the study (not number of days).

Does anyone know how I can merge in variables that are the same value across my keyed variable (i.e. person ID), but repeated multiple times for each day (aka case), in a level 1 dataset, to a level 2 dataset where there is only one case for each keyed variable (i.e. person ID)?  When I try to do a regular merge the new dataset has each day as a case, rather than each person.

Much appreciated.  I am sure others have had similar struggles.

David Disabato
George Mason University



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/A-many-to-one-merge-in-SPSS-tp5721596.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

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

[Robert Jones]

Presumably you use the aggregate command to create your table file.  If so, then it seems to me, according to the command syntax manual, that you can use the n or nu function/option to get the appropriate number of cases, then use that number as a weight when doing your analyses.

[Richard Ristow]

At 01:41 AM 8/16/2013, SabatoPsy wrote:

>Does anyone know how I can merge in variables that are the same
>value across my keyed variable (i.e. person ID), but repeated
>multiple times for each day (aka case), in a level 1 dataset, to a
>level 2 dataset where there is only one case for each keyed variable
>(i.e. person ID)?  When I try to do a regular merge the new dataset
>has each day as a case, rather than each

As you describe it, it's essentially impossible. It's impossible
because, if any variable has values that change between days within
person, neither SPSS nor any other software can tell, by itself, what
value to use. You have to do that.

Usually, in situations like this, you start by using AGGREGATE on
your 'level-1' dataset (with data for multiple days for each person),
with person ID as the BREAK variable, and *without*
"/MODE=ADDVARIABLES". That will give you a summary dataset with one
record per person; you can then do a one-to-one merge (MATCH FILES)
with your 'level-2' dataset (which already has one record per
person), and proceed.

Now, what values are in the summary file created by AGGREGATE? You'll
have to decide that. You can, for example, take the mean (within
person, across days) of variables in your level-1 data, and that may
serve your purpose. If it doesn't, look at AGGREGATE for other
summary statistics it can give; those include maximum, minimum, and
(in recent releases) median.

-Best of luck,
  Richard Ristow

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

[SabatoPsy]

Hello Richard:

Thank you for your suggestion.  I never knew that /MODE=ADDVARIABLES was one of many options for the aggregated command output.  I was able to create what I needed by saving the aggregated results to a temporary file that I merged into my level 2 dataset like you suggested.  For others who may read, I added a syntax line with /OUTFILE="C:/Users/..." on the line right below AGGREGATE and before /BREAK. 

David Disabato

On Fri, Aug 16, 2013 at 3:07 PM, Richard Ristow [via SPSSX Discussion] <[hidden email]> wrote:

At 01:41 AM 8/16/2013, SabatoPsy wrote:

>Does anyone know how I can merge in variables that are the same
>value across my keyed variable (i.e. person ID), but repeated
>multiple times for each day (aka case), in a level 1 dataset, to a
>level 2 dataset where there is only one case for each keyed variable
>(i.e. person ID)?  When I try to do a regular merge the new dataset
>has each day as a case, rather than each

As you describe it, it's essentially impossible. It's impossible
because, if any variable has values that change between days within
person, neither SPSS nor any other software can tell, by itself, what
value to use. You have to do that.

Usually, in situations like this, you start by using AGGREGATE on
your 'level-1' dataset (with data for multiple days for each person),
with person ID as the BREAK variable, and *without*
"/MODE=ADDVARIABLES". That will give you a summary dataset with one
record per person; you can then do a one-to-one merge (MATCH FILES)
with your 'level-2' dataset (which already has one record per
person), and proceed.

Now, what values are in the summary file created by AGGREGATE? You'll
have to decide that. You can, for example, take the mean (within
person, across days) of variables in your level-1 data, and that may
serve your purpose. If it doesn't, look at AGGREGATE for other
summary statistics it can give; those include maximum, minimum, and
(in recent releases) median.

-Best of luck,
  Richard Ristow

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

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/A-many-to-one-merge-in-SPSS-tp5721596p5721626.html

To unsubscribe from A 'many to one' merge in SPSS, click here.
NAML

--
David J. Disabato

Clinical Psychology Doctoral Student

George Mason University

[hidden email]

[Richard Ristow]

A footnote I should have added earlier,

At 01:59 AM 8/19/2013, SabatoPsy wrote:

>I was able to create what I needed by saving the
>aggregated results to a temporary file that I
>merged into my level 2 dataset like you
>suggested. Â For others who may read, I added a
>syntax line with /OUTFILE="C:/Users/..." on the
>line right below AGGREGATE and before /BREAK.

That will work just fine, and is one good way to
do it. In current versions of SPSS, you can also
use an SPSS 'dataset' instead of the scratch disk file:

DATASET DECLARE Aggy.
AGGREGATE
    /OUTFILE=    Aggy
    /BREAK  = ....

You can then read the dataset 'Aggy' (use any
name you like) on MATCH FILES or ADD FILES
commands exactly as if it were a disk file. (You
can't, however, read a dataset with GET FILE. It's a long story...)

Two advantages of a dataset over a scratch disk
file are that the dataset doesn't have to be
erased after use, and that the syntax using a
dataset is more portable between systems (because
you don't have one system's directory structure as part of your code.)

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