SPSSX Discussion

Link files and add variables

Classic

List

Threaded

31 messages Options

spssdummy

Link files and add variables

I added two files together based on the variable date, using ''merge files''.
However:
File 1 contains some dates that occur multiple times. If this is the case,
the data from the variables of file 2 need to be linked multiple times to
the cases with the same dates (even though they are different cases, they
need the same information/values obtained from file 2). File 2 contains all
dates just once (independent of file 1) and also contains extra dates that
are not needed. What I want is that all data from file 2 is matched exactly
to file 1 based on date, so if this date is once in file 1, the matching
data from file 2 is needed only once, and if it is in there multiple times,
it is also needed multiple times.

What happens tho is that it is matched, but if the date occurs multiple
times, then only the first date ''receives'' data from file 2, and the other
(same) dates are left empty for the variables obtained from file 2. Also all
the dates that are not in file 1 but only in file 2 are included in the
merged file and they shouldn't be included in there.

Is there a solution for this in SPSS?

--
Sent from: http://spssx-discussion.1045642.n5.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

Ki Park

Re: Link files and add variables

I think you could use MATCH FILES. Instead of "FILE" to "FILE" use the
TABLE.

*Match files one to many merge (first file have repeated matching id=dates).

MATCH FILES /FILE="data1.sav"
/TABLE="data2.sav" */this is the difference: “Table”
/BY date.

--
Sent from: http://spssx-discussion.1045642.n5.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

Ki Park

Re: Link files and add variables

In reply to this post by spssdummy

MATCH FILES /FILE="data1_with_repeated_dates.sav"
/TABLE="data2_unique_dates.sav" */this is the
difference: “Table”
/BY date.

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso

Re: Link files and add variables

Administrator

Yep. That is indeed the simplest solution.

Ki Park wrote

> MATCH FILES /FILE="data1_with_repeated_dates.sav"
> /TABLE="data2_unique_dates.sav" */this is the
> difference: “Table”
> /BY date.
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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

-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
--
Sent from: http://spssx-discussion.1045642.n5.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

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

spssdummy

Re: Link files and add variables

In reply to this post by Ki Park

Is the */this is the difference: ''Table'' part of the syntax? I get a lot of
errors. When removing that I get all the variables but no values.

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso

Re: Link files and add variables

Administrator

Run a procedure or EXECUTE. MATCH is a transformation and as such does not
pass the data.

spssdummy wrote

> Is the */this is the difference: ''Table'' part of the syntax? I get a lot
> of
> errors. When removing that I get all the variables but no values.
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

spssdummy

Re: Link files and add variables

Okay so I used execute and the result is a little bit better. New file
contains everything from file with repeated dates so that is perfect but
only some random cases from the file with unique dates have been added and
most are missing.

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso

Re: Link files and add variables

Administrator

OK. By design.
DATA LIST FREE / id x.
BEGIN DATA
1 1
1 2
2 1
3 4
END DATA.
DATASET NAME a.
DATA LIST FREE / id a b c.
BEGIN DATA
1 1 2 3
2 1 4 6
4 6 6 6
END DATA.
DATASET NAME tab.

/* Original problem (a b c appear only appear on first occurance) */.

MATCH FILES /FILE a /FILE tab / BY id.
LIST.
id x a b c

1.00 1.00 1.00 2.00 3.00
1.00 2.00 . . .
2.00 1.00 1.00 4.00 6.00
3.00 4.00 . . .
4.00 . 6.00 6.00 6.00

/* New problem (id EQ 4 from "TABLE" is missing) */.

MATCH FILES /FILE a /TABLE tab / BY id.
LIST.

id x a b c

1.00 1.00 1.00 2.00 3.00
1.00 2.00 1.00 2.00 3.00
2.00 1.00 1.00 4.00 6.00
3.00 4.00 . . .

/* Resolution */.
MATCH FILES /FILE a /FILE tab / BY id.
DO IF (id EQ LAG(id)).
LEAVE ALL.
END IF.
LIST.
id x a b c

1.00 1.00 1.00 2.00 3.00
1.00 2.00 1.00 2.00 3.00
2.00 1.00 1.00 4.00 6.00
3.00 4.00 1.00 4.00 6.00
4.00 4.00 6.00 6.00 6.00

spssdummy wrote

> Okay so I used execute and the result is a little bit better. New file
> contains everything from file with repeated dates so that is perfect but
> only some random cases from the file with unique dates have been added and
> most are missing.
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

Maguin, Eugene

Re: Link files and add variables

David, nice demonstration of the use of the Leave command. Thanks, Gene Maguin

-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of David Marso
Sent: Wednesday, May 22, 2019 8:35 AM
To: [hidden email]
Subject: Re: Link files and add variables

OK. By design.
DATA LIST FREE / id x.
BEGIN DATA
1 1
1 2
2 1
3 4
END DATA.
DATASET NAME a.
DATA LIST FREE / id a b c.
BEGIN DATA
1 1 2 3
2 1 4 6
4 6 6 6
END DATA.
DATASET NAME tab.

/* Original problem (a b c appear only appear on first occurance) */.

MATCH FILES /FILE a /FILE tab / BY id.
LIST.
id x a b c

1.00 1.00 1.00 2.00 3.00
1.00 2.00 . . .
2.00 1.00 1.00 4.00 6.00
3.00 4.00 . . .
4.00 . 6.00 6.00 6.00

/* New problem (id EQ 4 from "TABLE" is missing) */.

MATCH FILES /FILE a /TABLE tab / BY id.
LIST.

id x a b c

1.00 1.00 1.00 2.00 3.00
1.00 2.00 1.00 2.00 3.00
2.00 1.00 1.00 4.00 6.00
3.00 4.00 . . .

/* Resolution */.
MATCH FILES /FILE a /FILE tab / BY id.
DO IF (id EQ LAG(id)).
LEAVE ALL.
END IF.
LIST.
id x a b c

1.00 1.00 1.00 2.00 3.00
1.00 2.00 1.00 2.00 3.00
2.00 1.00 1.00 4.00 6.00
3.00 4.00 1.00 4.00 6.00
4.00 4.00 6.00 6.00 6.00

spssdummy wrote

> Okay so I used execute and the result is a little bit better. New file
> contains everything from file with repeated dates so that is perfect
> but only some random cases from the file with unique dates have been
> added and most are missing.
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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

-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
--
Sent from: http://spssx-discussion.1045642.n5.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

spssdummy

Re: Link files and add variables

In reply to this post by David Marso

Thanks for your suggestions! I just don't really know what all of that means
in your last reply bc I'm not that experienced with this stuff.

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso

Re: Link files and add variables

Administrator

RTFM?
LEAVE
LEAVE varlist
This command does not read the active dataset. It is stored, pending
execution with the next command that reads the data set.

Example
COMPUTE TSALARY=TSALARY+SALARY. LEAVE TSALARY. FORMAT TSALARY (DOLLAR8)/
SALARY (DOLLAR7). EXECUTE.

*Overview*
Normally, the program reinitializes variables each time it prepares to read
a new case. LEAVE suppresses reinitialization and retains the current value
of the specified variable or variables when the program reads the next case.
It also sets the initial value received by a numeric variable to 0 instead
of system-missing. LEAVE is frequently used with COMPUTE to create a
variable to store an accumulating sum. LEAVE is also used to spread a
variable’s values across multiple cases when VECTOR is used within an input
program to restructure a data file. LEAVE cannot be used with scratch
variables. For more information, see the topic Scratch Variables on p. 52.
Basic Specification
The basic specification is the variable(s) whose values are not to be
reinitialized as each new case is read.
Syntax Rules 
Variables named on LEAVE must be new variables that do not already exist in
the active dataset prior to the transformation block that defines them, but
they must be defined in the transformationblockpriortothe LEAVE
commandthatspecifiesthem. Formoreinformation, see the topic Examples on p.
1057.  Variables named on LEAVE cannot be scratch variables (but scratch
variables can be used to obtain functionality equivalent to LEAVE). For more
information, see the topic Scratch Variables on p. 52. 
Multiplevariablescanbenamed. Thekeyword TO
canbeusedtorefertoalistofconsecutive variables. 
Stringandnumericvariablescanbespecifiedonthe same LEAVE command.
Operations  Numericvariablesnamedon LEAVE
areinitializedto0forthefirstcase,andstringvariables areinitializedtoblanks.
Thesevariablesarenotreinitializedwhennewcasesareread.
© Copyright IBM Corporation 1989, 2011. 1056
1057 LEAVE
Examples
Correct vs. Invalid Specifications for LEAVE
DATA LIST LIST /Var1 Var2 Var3. BEGIN DATA 123 456 789 END DATA.
*this is the correct form. COMPUTE TotalVar1=TotalVar1+Var1. LEAVE
TotalVar1.
*this will change the value of Var2 but LEAVE will fail, generating an error
because Var2 already exists. COMPUTE Var2=Var2+Var2. LEAVE Var2.
*this will fail, generating an error because the LEAVE command occurs before
the command that defines the variable named on LEAVE. LEAVE TotalVar3.
COMPUTE TotalVar3=TotalVar3+Var3.
LIST.
Running Total
COMPUTE TSALARY=TSALARY+SALARY. LEAVE TSALARY. FORMAT TSALARY (DOLLAR8)/
SALARY (DOLLAR7).
 These commands keep a running total of salaries across all cases. SALARY
is the variable
containingtheemployee’ssalary,andTSALARYisthenewvariablecontainingthecumulative
salaries for all previous cases. 
Forthefirstcase,TSALARYisinitializedto0,andTSALARYequalsSALARY.Fortherestof
the cases, TSALARY stores the cumulative totals forSALARY.  LEAVE follows
COMPUTE becauseTSALARYmust first be defined before it can be specified on
LEAVE.  If LEAVE were not specified for this computation, TSALARY would be
initialized to system-missing for all cases. TSALARY would remain
system-missing because its value would be missing for every computation.
Separate Sums for Each Category of a Grouping Variable
SORT CASES DEPT. IF DEPT NE LAG(DEPT,1) TSALARY=0. /*Initialize for new dept
COMPUTE TSALARY=TSALARY+SALARY. /*Sum salaries LEAVE TSALARY. /*Prevent
initialization each case FORMAT TSALARY (DOLLAR8)/ SALARY (DOLLAR7).
 These commandsaccumulate a sumacrosscasesforeach department.  SORT first
sorts cases by the values of variable DEPT.
1058 LEAVE
 IF specifiesthat ifthe valueofDEPTforthe currentcase isnotequal
tothevalueofDEPT for the previouscase, TSALARYequals 0. Thus,TSALARY isreset
to 0 each time the value of DEPT changes. (For the first case in the file,
the logical expression on IF is missing. However, the desired effect is
obtained because LEAVE initializes TSALARY to 0 for the first case,
independent of the IF statement.)  LEAVE
preventsTSALARYfrombeinginitializedforcaseswithinthesamedepartment

spssdummy wrote

> Thanks for your suggestions! I just don't really know what all of that
> means
> in your last reply bc I'm not that experienced with this stuff.
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

David Marso-2

Re: Link files and add variables

In reply to this post by spssdummy

What are you doing to remedy that?

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

Kirill Orlov

Re: Link files and add variables

In reply to this post by Maguin, Eugene

Agree. LEAVE without COMPUTE - elegant.

22.05.2019 16:09, Maguin, Eugene пишет:

David, nice demonstration of the use of the Leave command. Thanks, 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

spssdummy

Re: Link files and add variables

In reply to this post by David Marso-2

Thanks, I made some progress.
Unfortunately, there are really a lot of duplicates and also dates that are
only in one dataset and not in the Original. I don't know how i can
perfectly match the data.

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso

Re: Link files and add variables

Administrator

Post a mockup of the data situation and the desired outcome.
Alternatively attach the files to this thread.
http://spssx-discussion.1045642.n5.nabble.com/template/NamlServlet.jtp?macro=reply&node=5737945
Also include your syntax and any error messages.

spssdummy wrote

> Thanks, I made some progress.
> Unfortunately, there are really a lot of duplicates and also dates that
> are
> only in one dataset and not in the Original. I don't know how i can
> perfectly match the data.
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

spssdummy

Re: Link files and add variables

File 1: Physical activity data --> Contains the variable called VISIT1_DATE
which is the date that the study participant came to perform measuremens:
- Includes random dates between 2010 and 2013 --> Some dates are not
included, others are included once and others are included two or more
times, as different participants could have come on the same date.
- File also contains various continuous dependent variables and some
categorical variables such as gender.

File 2: Weather data --> Contains information about the weather:
- Also includes the variable VISIT1_DATE (I gave it the same name as in the
other file). However this file contains only weather information, and dates
from 1906 to 2019 are included. Every date is just included once.
- Contains 7 dependent continuous weather variables.

Desired outcome:
- The final file only contains the dates in file. All the weather data from
file 2 have to become matched to the cases in file 1 based on VISIT1_DATE
--> When date in file 1 is 1-1-2011, the weather data from file 2 from
1-1-2011 have to be matched. When this date is in file 1 multiple times, the
same weather data is linked to all these cases (as the weather is the same
on the same dates). Data from dates between 2010 and 2013 that are not in
file 1 (so that are only in file 2) are not needed and should not be
included in the final file.

Syntax:
MATCH FILES /FILE 'physicalactivitydata.sav' /FILE 'weatherdata.sav' /BY
VISIT1_DATE.
DO IF (VISIT1_DATE EQ LAG(VISIT1_DATE)).
LEAVE ALL.
END IF.
LIST.

Error messages when using this syntax:
279 LIST Duplicate key in a file. The BY variables do not uniquely
identify each case on the indicated file. Please check the results
carefully.

--
Sent from: http://spssx-discussion.1045642.n5.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

spssdummy

Re: Link files and add variables

And also data from dates before 2010 and after 2013 are not needed of course
(basically everything that is not in file 1 should be removed)

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso-2

Re: Link files and add variables

In reply to this post by spssdummy

Ki Park posted the solution days ago. Please elaborate on what exactly is insufficient.
"I think you could use MATCH FILES. Instead of "FILE" to "FILE" use the
TABLE.

*Match files one to many merge (first file have repeated matching id=dates)."

Edited removed inline comment.

MATCH FILES /FILE="data1.sav"
/TABLE="data2.sav"
/BY date.

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

spssdummy

Re: Link files and add variables

Thanks. Okay so I used this syntax now:
MATCH FILES /FILE=*
/TABLE='DataSet2'
/BY VISIT1_DATE.
EXECUTE.

Basically only a few of the cases of the weather file are actually being
added to the original file. The rest of the lines stay empty for the newly
added variables.

--
Sent from: http://spssx-discussion.1045642.n5.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

David Marso

Re: Link files and add variables

Administrator

Perhaps attach the actual data files as previously requested.
Maybe the dates don't match exactly (ie there is a time component?).

spssdummy wrote

> Thanks. Okay so I used this syntax now:
> MATCH FILES /FILE=*
> /TABLE='DataSet2'
> /BY VISIT1_DATE.
> EXECUTE.
>
> Basically only a few of the cases of the weather file are actually being
> added to the original file. The rest of the lines stay empty for the newly
> added variables.
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to