SPSSX Discussion

incomplete merge

Classic

List

Threaded

12 messages Options

Matthew Pirritano

incomplete merge

All,

I am having a most vexing problem.

I have done many merges in my day. But for some reason this one is not working.

I have three files. A male file, a female file, and a full file that the male and female files are being added to.

I've placed the id field from each file that I am matching on next to each other in excel and lo and behold they all match up. But when I run a match I wind up with 145 missing fields. The fields are 10 character string fields. I do matches like this on ssn in my sleep. What in the world could be going on here.

Since the full file has both male and female data I'm doing a table look up with the male or female file as the table. I actually don't think this should matter, since the full file should match up with only one line each from the male and female file. I've tried it without the table lookup and get the same results.

I've also tried to trim each field, thinking there might be some blanks. That didn't help.

Please someone help me see what I'm missing.

Thanks
Matt

Matthew Pirritano, Ph.D.
Email: [hidden email]

Richard Ristow

Re: incomplete merge

At 08:25 PM 10/2/2010, Matthew Pirritano wrote:

>I have three files. A male file, a female file, and a full file that
>the male and female files are being added to. I've placed the id
>field from each file that I am matching on next to each other in
>excel and lo and behold they all match up. But when I run a match I
>wind up with 145 missing fields.
>
>Since the full file has both male and female data I'm doing a table
>look up with the male or female file as the table.

Could you post the exact syntax you're running?

I can't completely make sense of what you're writing; here are some questions:

>"I have a male file, a female file, and a full file that the male
>and female files are being added to."

Does each record in each file represent a *person*, or something else?

Are you adding *variables* from the 'male' and 'female' files to the
full file (MATCH FILES)? Or are you adding *cases*, or records (ADD FILES)?

>"when I run a match I wind up with 145 missing fields."

Do you mean 145 missing id values? If so, you write

>"I've placed the id field from each file that I am matching on next
>to each other in excel and they all match up."

That's a very inefficient way to check whether your keys all match.

To check matches from a MATCH FILES (it sounds likely to me you're
running a MATCH FILES), specify an "/IN=" variable for each input
file on the MATCH FILES statement. Then you can use SELECT IF on
those "/IN=" variables to select records that occur in one file, or
another, or both. If you expect all records to match, this will
quickly identify any failures.

By the way, 145 non-matches out of how many total records? 145 out of
200? out of 2,000? out of 2,000,000?

>Since the full file has both male and female data I'm doing a table
>look up with the male or female file as the table.

That is NOT appropriate unless id field values can occur more than
once in the full file, but only once each in the male and female
files. If each record does represent a person, an id field value
occurring more than once should mean a person entered in duplicate,
which should be an error.

>I've also tried to trim each field, thinking there might be some
>blanks. That didn't help.

It's easy enough to get quite simple typos in id fields in big files,
and that may be your problem. If so, using SPSS to identify and list
records that don't match should find them.

-Good luck to you,
Richard

=====================
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: incomplete merge

Administrator

In reply to this post by Matthew Pirritano

Hey Matt,
Please post your SPSS code.
Difficult to do ESP on this one.
Respects, David

On Sat, 2 Oct 2010 17:25:32 -0700, Matthew Pirritano
<[hidden email]> wrote:

>All,
>
>I am having a most vexing problem.
>
>I have done many merges in my day. But for some reason this one is not working.
>
>I have three files. A male file, a female file, and a full file that the male
>and female files are being added to.
>
>I've placed the id field from each file that I am matching on next to each

other
>in excel and lo and behold they all match up. But when I run a match I wind up
>with 145 missing fields. The fields are 10 character string fields. I do
matches
>like this on ssn in my sleep. What in the world could be going on here.
>
>
>Since the full file has both male and female data I'm doing a table look up
with
>the male or female file as the table. I actually don't think this should
matter,

>since the full file should match up with only one line each from the male and
>female file. I've tried it without the table lookup and get the same results.
>
>I've also tried to trim each field, thinking there might be some blanks. That
>didn't help.
>
>
>Please someone help me see what I'm missing.
>
>Thanks
>Matt
>
> Matthew Pirritano, Ph.D.
>Email: [hidden email]
>

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?"

Matthew Pirritano

Re: incomplete merge

In reply to this post by Richard Ristow

Here's the syntax:

dataset activate full_file.
sort cases by id.

dataset activate male.
sort cases by id.

dataset activate female.
sort cases by id.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/TABLE='male'
/BY ID.
EXECUTE.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/TABLE='female'
/BY ID.
EXECUTE.

There are a total of 249 participants in the full file. The female file contains 141 and the male file 108.

I know that there are no typos because, like I said, I put the id's into excel, side by side. The id's are numbers, even though they're saved as strings in the file. So I simply subtract them in the excel file. I get all zeros. So, no, there can't be any typos.

Here's why I'm doing this match. This is survey monkey data. When I initially downloaded the data a few of the variables did not include all data. For some reason these few variables that had scales of 0 to 10 (11 points) only had recorded the lowest (0) and highest (10) scores. When I downloaded a different version of the data, called 'expanded' by survey monkey, those items had all of the data, not just the extreme responses. But now the file was getting truncated at the excel limit of 256 columns (excluding a bunch of variables at the end of the file; the expanded version gives every response option a column, hence the file gets much wider), even though I'm opening up a csv in spss. Seems like the column limit is a survey monkey thing. I've contacted them about that.

But that leaves me with one file that has complete data for all variables but the 0 to 10 point variables (File A). So I wanted to add the 0 to 10 point variables from the expanded file (File B) to File A.

I know that the table look up doesn't make sense. I was just trying things.

Thanks
Matt

Matthew Pirritano, Ph.D.
Email: [hidden email]

From: Richard Ristow <[hidden email]>
To: [hidden email]
Sent: Sat, October 2, 2010 8:49:57 PM
Subject: Re: incomplete merge

At 08:25 PM 10/2/2010, Matthew Pirritano wrote:

>I have three files. A male file, a female file, and a full file that
>the male and female files are being added to. I've placed the id
>field from each file that I am matching on next to each other in
>excel and lo and behold they all match up. But when I run a match I
>wind up with 145 missing fields.
>
>Since the full file has both male and female data I'm doing a table
>look up with the male or female file as the table.

Could you post the exact syntax you're running?

I can't completely make sense of what you're writing; here are some questions:

>"I have a male file, a female file, and a full file that the male
>and female files are being added to."

Does each record in each file represent a *person*, or something else?

Are you adding *variables* from the 'male' and 'female' files to the
full file (MATCH FILES)? Or are you adding *cases*, or records (ADD FILES)?

>"when I run a match I wind up with 145 missing fields."

Do you mean 145 missing id values? If so, you write

>"I've placed the id field from each file that I am matching on next
>to each other in excel and they all match up."

That's a very inefficient way to check whether your keys all match.

To check matches from a MATCH FILES (it sounds likely to me you're
running a MATCH FILES), specify an "/IN=" variable for each input
file on the MATCH FILES statement. Then you can use SELECT IF on
those "/IN=" variables to select records that occur in one file, or
another, or both. If you expect all records to match, this will
quickly identify any failures.

By the way, 145 non-matches out of how many total records? 145 out of
200? out of 2,000? out of 2,000,000?

>Since the full file has both male and female data I'm doing a table
>look up with the male or female file as the table.

That is NOT appropriate unless id field values can occur more than
once in the full file, but only once each in the male and female
files. If each record does represent a person, an id field value
occurring more than once should mean a person entered in duplicate,
which should be an error.

>I've also tried to trim each field, thinking there might be some
>blanks. That didn't help.

It's easy enough to get quite simple typos in id fields in big files,
and that may be your problem. If so, using SPSS to identify and list
records that don't match should find them.

-Good luck to you,
Richard

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

Matthew Pirritano

Re: incomplete merge

In reply to this post by David Marso

Exact same thing happens regardless if I use TABLE or FILE in my matching syntax.

There is only one line per participant so FILE should work.

dataset activate full_file.
sort cases by id.

dataset activate male.
sort cases by id.

dataset activate female.
sort cases by id.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/FILE='male'
/BY ID.
EXECUTE.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/FILE='female'
/BY ID.
EXECUTE.

Matthew Pirritano, Ph.D.
Email: [hidden email]

From: David Marso <[hidden email]>
To: [hidden email]; subscribe spssx-l Matthew Pirritano <[hidden email]>
Cc: David Marso <[hidden email]>
Sent: Sat, October 2, 2010 10:02:19 PM
Subject: Re: incomplete merge

Hey Matt,
Please post your SPSS code.
Difficult to do ESP on this one.
Respects, David

On Sat, 2 Oct 2010 17:25:32 -0700, Matthew Pirritano
<[hidden email]> wrote:

Art Kendall

Re: incomplete merge

Just curious.
How does survey monkey format the data it provides? Are there options that would make it unnecessary to go through EXCEL? csv, fixed columns, tab separated, pipe separated, etc.?

Art Kendall
Social Research Consultants

On 10/3/2010 1:05 AM, Matthew Pirritano wrote:

Exact same thing happens regardless if I use TABLE or FILE in my matching syntax.

There is only one line per participant so FILE should work.

dataset activate full_file.
sort cases by id.

dataset activate male.
sort cases by id.

dataset activate female.
sort cases by id.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/FILE='male'
/BY ID.
EXECUTE.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/FILE='female'
/BY ID.
EXECUTE.

Matthew Pirritano, Ph.D.
Email: [hidden email]

From: David Marso [hidden email]
To: [hidden email]; subscribe spssx-l Matthew Pirritano [hidden email]
Cc: David Marso [hidden email]
Sent: Sat, October 2, 2010 10:02:19 PM
Subject: Re: incomplete merge

Hey Matt,
Please post your SPSS code.
Difficult to do ESP on this one.
Respects, David

On Sat, 2 Oct 2010 17:25:32 -0700, Matthew Pirritano
<[hidden email]> wrote:

>All,
>
>I am having a most vexing problem.
>
>I have done many merges in my day. But for some reason this one is not working.
>
>I have three files. A male file, a female file, and a full file that the male
>and female files are being added to.
>
>I've placed the id field from each file that I am matching on next to each
other
>in excel and lo and behold they all match up. But when I run a match I wind up
>with 145 missing fields. The fields are 10 character string fields. I do
matches
>like this on ssn in my sleep. What in the world could be going on here.
>
>
>Since the full file has both male and female data I'm doing a table look up
with
>the male or female file as the table. I actually don't think this should
matter,
>since the full file should match up with only one line each from the male and
>female file. I've tried it without the table lookup and get the same results.
>
>I've also tried to trim each field, thinking there might be some blanks. That
>didn't help.
>
>
>Please someone help me see what I'm missing.
>
>Thanks
>Matt
>
> Matthew Pirritano, Ph.D.
>Email: [hidden email]
>

Art Kendall
Social Research Consultants

Bruce Weaver

Re: incomplete merge

Administrator

In reply to this post by Matthew Pirritano

Matthew Pirritano wrote

Here's the syntax:

dataset activate full_file.
sort cases by id.

dataset activate male.
sort cases by id.

dataset activate female.
sort cases by id.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/TABLE='male'
/BY ID.
EXECUTE.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/TABLE='female'
/BY ID.
EXECUTE.

There are a total of 249 participants in the full file. The female file
contains 141 and the male file 108.

I know that there are no typos because, like I said, I put the id's into excel,
side by side. The id's are numbers, even though they're saved as strings in the
file. So I simply subtract them in the excel file. I get all zeros. So, no,
there can't be any typos.

Here's why I'm doing this match. This is survey monkey data. When I initially
downloaded the data a few of the variables did not include all data. For some
reason these few variables that had scales of 0 to 10 (11 points) only had
recorded the lowest (0) and highest (10) scores. When I downloaded a different
version of the data, called 'expanded' by survey monkey, those items had all of
the data, not just the extreme responses. But now the file was getting truncated
at the excel limit of 256 columns (excluding a bunch of variables at the end of
the file; the expanded version gives every response option a column, hence the
file gets much wider), even though I'm opening up a csv in spss. Seems like the
column limit is a survey monkey thing. I've contacted them about that.

But that leaves me with one file that has complete data for all variables but
the 0 to 10 point variables (File A). So I wanted to add the 0 to 10 point
variables from the expanded file (File B) to File A.

I know that the table look up doesn't make sense. I was just trying things.

Thanks
Matt

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

David Marso

Re: incomplete merge

Administrator

In reply to this post by Matthew Pirritano

Bruce :"I'm thinking there may be leading or trailing blanks that
>are lousing things up."
Great minds think alike! I was just going to post the same suggestion.
I don't believe trailing blanks will be a problem, but leading blanks will
certainly seriously hose the situation and would likely NOT be detectable in
Excel (depending upon column formatting). Question re multiple TABLE
subcommands? Perfectly fine!
HTH, David
---
data list /id (a2) b (F1) c (F1).
begin data
a 12
b 14
c 25
d 26
end data.
sort cases by id.
save outfile "C:\TEMP\fullfile".
data list /id (a2) d (F1) e(F1).
begin data
a12
b 14
end data.
sort cases by id.
save outfile "C:\TEMP\males".
data list /id (a2) d (F1) e(F1).
begin data
c 25
d26
end data.
sort cases by id.
save outfile "C:\TEMP\females".
match files
/ file "C:\TEMP\fullfile"
/ table "C:\TEMP\males"
/ table "C:\TEMP\females"
/ by ID.
freq all.
LIST.

ID B C D E

a 1 2 . .
b 1 4 1 4
c 2 5 2 5
d 2 6 . .

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

>

n Sun, 3 Oct 2010 06:08:52 -0700, Bruce Weaver <[hidden email]> wrote:

>Matthew Pirritano wrote:
>>
>> Here's the syntax:
>>
>> dataset activate full_file.
>> sort cases by id.
>>
>> dataset activate male.
>> sort cases by id.
>>
>> dataset activate female.
>> sort cases by id.
>>
>>
>> DATASET ACTIVATE full_file.
>> MATCH FILES /FILE=*
>> /TABLE='male'
>> /BY ID.
>> EXECUTE.
>>
>>
>> DATASET ACTIVATE full_file.
>> MATCH FILES /FILE=*
>> /TABLE='female'
>> /BY ID.
>> EXECUTE.
>>
>> There are a total of 249 participants in the full file. The female file
>> contains 141 and the male file 108.
>>
>>
>> I know that there are no typos because, like I said, I put the id's into
>> excel,
>> side by side. The id's are numbers, even though they're saved as strings
>> in the
>> file. So I simply subtract them in the excel file. I get all zeros. So,
>> no,
>> there can't be any typos.
>>
>> Here's why I'm doing this match. This is survey monkey data. When I
>> initially
>> downloaded the data a few of the variables did not include all data. For
>> some
>> reason these few variables that had scales of 0 to 10 (11 points) only had
>> recorded the lowest (0) and highest (10) scores. When I downloaded a
>> different
>> version of the data, called 'expanded' by survey monkey, those items had
>> all of
>> the data, not just the extreme responses. But now the file was getting
>> truncated
>> at the excel limit of 256 columns (excluding a bunch of variables at the
>> end of
>> the file; the expanded version gives every response option a column, hence
>> the
>> file gets much wider), even though I'm opening up a csv in spss. Seems
>> like the
>> column limit is a survey monkey thing. I've contacted them about that.
>>
>>
>> But that leaves me with one file that has complete data for all variables
>> but
>> the 0 to 10 point variables (File A). So I wanted to add the 0 to 10 point
>> variables from the expanded file (File B) to File A.
>>
>>
>> I know that the table look up doesn't make sense. I was just trying
>> things.
>>
>> Thanks
>> Matt
>>
>>
>
>Hi Matt. You said, "The id's are numbers, even though they're saved as
>strings in the file." I'd try converting them to numeric before merging
>files--you could throw in an ALTER TYPE command where you sort by ID for
>each data set. I'm thinking there may be leading or trailing blanks that
>are lousing things up.
>
>Also, can you have two /TABLE sub-commands in one MATCH FILES command?
>I.e., does this work?
>
>DATASET ACTIVATE full_file.
>MATCH FILES /FILE=*
> /TABLE='male'
> /TABLE='female'
> /BY ID.
>EXECUTE.
>
>I don't have SPSS on this machine, so I can't try it right now.
>
>
>
>-----
>--
>Bruce Weaver
>[hidden email]
>http://sites.google.com/a/lakeheadu.ca/bweaver/
>
>"When all else fails, RTFM."
>
>NOTE: My Hotmail account is not monitored regularly.
>To send me an e-mail, please use the address shown above.
>
>--
>View this message in context:

http://spssx-discussion.1045642.n5.nabble.com/incomplete-merge-tp3074240p3074535.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

Matthew Pirritano

Re: incomplete merge

In reply to this post by Bruce Weaver

This data is a csv. I did not have to go through excel.

Bruce, you were right. I merged with the id as numeric and it worked. But why?! I hate things that don't make sense. I had already ltrim and rtrimmed the variable so there couldn't have been trailing or leading spaces, right?

Oh, well. Thanks for all the help! sanity has been restored.

Thanks
Matt

Matthew Pirritano, Ph.D.
Email: [hidden email]

From: Bruce Weaver <[hidden email]>
To: [hidden email]
Sent: Sun, October 3, 2010 6:08:52 AM
Subject: Re: incomplete merge

Matthew Pirritano wrote:

>
> Here's the syntax:
>
> dataset activate full_file.
> sort cases by id.
>
> dataset activate male.
> sort cases by id.
>
> dataset activate female.
> sort cases by id.
>
>
> DATASET ACTIVATE full_file.
> MATCH FILES /FILE=*
> /TABLE='male'
> /BY ID.
> EXECUTE.
>
>
> DATASET ACTIVATE full_file.
> MATCH FILES /FILE=*
> /TABLE='female'
> /BY ID.
> EXECUTE.
>
> There are a total of 249 participants in the full file. The female file
> contains 141 and the male file 108.
>
>
> I know that there are no typos because, like I said, I put the id's into
> excel,
> side by side. The id's are numbers, even though they're saved as strings
> in the
> file. So I simply subtract them in the excel file. I get all zeros. So,
> no,
> there can't be any typos.
>
> Here's why I'm doing this match. This is survey monkey data. When I
> initially
> downloaded the data a few of the variables did not include all data. For
> some
> reason these few variables that had scales of 0 to 10 (11 points) only had
> recorded the lowest (0) and highest (10) scores. When I downloaded a
> different
> version of the data, called 'expanded' by survey monkey, those items had
> all of
> the data, not just the extreme responses. But now the file was getting
> truncated
> at the excel limit of 256 columns (excluding a bunch of variables at the
> end of
> the file; the expanded version gives every response option a column, hence
> the
> file gets much wider), even though I'm opening up a csv in spss. Seems
> like the
> column limit is a survey monkey thing. I've contacted them about that.
>
>
> But that leaves me with one file that has complete data for all variables
> but
> the 0 to 10 point variables (File A). So I wanted to add the 0 to 10 point
> variables from the expanded file (File B) to File A.
>
>
> I know that the table look up doesn't make sense. I was just trying
> things.
>
> Thanks
> Matt
>
>

Hi Matt. You said, "The id's are numbers, even though they're saved as
strings in the file." I'd try converting them to numeric before merging
files--you could throw in an ALTER TYPE command where you sort by ID for
each data set. I'm thinking there may be leading or trailing blanks that
are lousing things up.

Also, can you have two /TABLE sub-commands in one MATCH FILES command?
I.e., does this work?

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/TABLE='male'
/TABLE='female'
/BY ID.
EXECUTE.

I don't have SPSS on this machine, so I can't try it right now.

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/incomplete-merge-tp3074240p3074535.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

Matthew Pirritano

Re: incomplete merge

In reply to this post by David Marso

Wishful thinking. It did not work! I accidentally ran a frequencies on only the female file.

I am still getting far too much missing data for the variables that I'm adding to the full file.

I am going to try and combine the male and female files first and then add to the full file.

thanks
Matt

Matthew Pirritano, Ph.D.
Email: [hidden email]

From: David Marso <[hidden email]>
To: [hidden email]
Sent: Sun, October 3, 2010 9:18:14 AM
Subject: Re: incomplete merge

Bruce :"I'm thinking there may be leading or trailing blanks that
>are lousing things up."
Great minds think alike! I was just going to post the same suggestion.
I don't believe trailing blanks will be a problem, but leading blanks will
certainly seriously hose the situation and would likely NOT be detectable in
Excel (depending upon column formatting). Question re multiple TABLE
subcommands? Perfectly fine!
HTH, David
---
data list /id (a2) b (F1) c (F1).
begin data
a 12
b 14
c 25
d 26
end data.
sort cases by id.
save outfile "C:\TEMP\fullfile".
data list /id (a2) d (F1) e(F1).
begin data
a12
b 14
end data.
sort cases by id.
save outfile "C:\TEMP\males".
data list /id (a2) d (F1) e(F1).
begin data
c 25
d26
end data.
sort cases by id.
save outfile "C:\TEMP\females".
match files
/ file "C:\TEMP\fullfile"
/ table "C:\TEMP\males"
/ table "C:\TEMP\females"
/ by ID.
freq all.
LIST.

ID B C D E

a 1 2 . .
b 1 4 1 4
c 2 5 2 5
d 2 6 . .

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

>

n Sun, 3 Oct 2010 06:08:52 -0700, Bruce Weaver <[hidden email]> wrote:

>http://sites.google.com/a/lakeheadu.ca/bweaver/
>
>"When all else fails, RTFM."
>
>NOTE: My Hotmail account is not monitored regularly.
>To send me an e-mail, please use the address shown above.
>
>--
>View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/incomplete-merge-tp3074240p3074535.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

mpirritano

Re: incomplete merge

In reply to this post by Art Kendall

For the sake of completeness I have to claim temporary insanity. What I was trying to do was ill founded. What I was trying to do is impossible in spss. I suddenly saw the light Sunday night as I sat reading. Let me explain.

The full file looks like this:

Id Gender

1 M

2 M

3 M

4          F
5          F
6          F

The male file contains data for males on var1 thru var4.

And the female file contains data for females on var1 thru var4.

So what I was trying to do was bring in the data for males for var1 1 thru var4, and then bring in the data for females for var1 thru var4. But of course after the variables were brought in for males the same variables with the same names could not be brought in for females! Of course I could have renamed the female variables and then transferred the data to be in the same columns as the male data. But I didn’t realize what I was doing.

Thanks for the help over weekend. I apologize for my obtuseness.

Thanks

Matt

Matthew Pirritano, Ph.D.

Research Analyst IV

Medical Services Initiative (MSI)

Orange County Health Care Agency

(714) 568-5648

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Art Kendall
Sent: Sunday, October 03, 2010 4:44 AM
To: [hidden email]
Subject: Re: incomplete merge

Matthew Pirritano, Ph.D.
Email: [hidden email]

From: David Marso [hidden email]
To: [hidden email]; subscribe spssx-l Matthew Pirritano [hidden email]
Cc: David Marso [hidden email]
Sent: Sat, October 2, 2010 10:02:19 PM
Subject: Re: incomplete merge

Hey Matt,
Please post your SPSS code.
Difficult to do ESP on this one.
Respects, David

On Sat, 2 Oct 2010 17:25:32 -0700, Matthew Pirritano
<[hidden email]> wrote:

>All,
>
>I am having a most vexing problem.
>
>I have done many merges in my day. But for some reason this one is not working.
>
>I have three files. A male file, a female file, and a full file that the male
>and female files are being added to.
>
>I've placed the id field from each file that I am matching on next to each
other
>in excel and lo and behold they all match up. But when I run a match I wind up
>with 145 missing fields. The fields are 10 character string fields. I do
matches
>like this on ssn in my sleep. What in the world could be going on here.
>
>
>Since the full file has both male and female data I'm doing a table look up
with
>the male or female file as the table. I actually don't think this should
matter,
>since the full file should match up with only one line each from the male and
>female file. I've tried it without the table lookup and get the same results.
>
>I've also tried to trim each field, thinking there might be some blanks. That
>didn't help.
>
>
>Please someone help me see what I'm missing.
>
>Thanks
>Matt
>
> Matthew Pirritano, Ph.D.
>Email: [hidden email]
>

Maguin, Eugene

Re: incomplete merge

Matt,

I understand that the problem is solved. While the sequential table commands
didn't work for the reason you stated, I think a table command followed by
an update command would have worked because the update command would have
replaced the sysmis values for females with the valid values from the female
data file. However, that's water over the bridge and convoluted too because
your final method seems more 'obvious'.

Gene Maguin

________________________________

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Pirritano, Matthew
Sent: Monday, October 04, 2010 10:43 AM
To: [hidden email]
Subject: Re: incomplete merge

For the sake of completeness I have to claim temporary insanity. What I was
trying to do was ill founded. What I was trying to do is impossible in spss.
I suddenly saw the light Sunday night as I sat reading. Let me explain.

The full file looks like this:

Id Gender

1 M

2 M

3 M

4 F
5 F
6 F

The male file contains data for males on var1 thru var4.

And the female file contains data for females on var1 thru var4.

So what I was trying to do was bring in the data for males for var1 1 thru
var4, and then bring in the data for females for var1 thru var4. But of
course after the variables were brought in for males the same variables with
the same names could not be brought in for females! Of course I could have
renamed the female variables and then transferred the data to be in the same
columns as the male data. But I didn't realize what I was doing.

Thanks for the help over weekend. I apologize for my obtuseness.

Thanks

Matt

Matthew Pirritano, Ph.D.

Research Analyst IV

Medical Services Initiative (MSI)

Orange County Health Care Agency

(714) 568-5648

________________________________

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Art
Kendall
Sent: Sunday, October 03, 2010 4:44 AM
To: [hidden email]
Subject: Re: incomplete merge

Just curious.
How does survey monkey format the data it provides? Are there options that
would make it unnecessary to go through EXCEL? csv, fixed columns, tab
separated, pipe separated, etc.?

Art Kendall
Social Research Consultants

On 10/3/2010 1:05 AM, Matthew Pirritano wrote:

Exact same thing happens regardless if I use TABLE or FILE in my matching
syntax.

There is only one line per participant so FILE should work.

dataset activate full_file.
sort cases by id.

dataset activate male.
sort cases by id.

dataset activate female.
sort cases by id.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/FILE='male'
/BY ID.
EXECUTE.

DATASET ACTIVATE full_file.
MATCH FILES /FILE=*
/FILE='female'
/BY ID.
EXECUTE.

Matthew Pirritano, Ph.D.
Email: [hidden email]

________________________________

From: David Marso <[hidden email]> <mailto:[hidden email]>
To: [hidden email]; subscribe spssx-l Matthew Pirritano
<[hidden email]> <mailto:[hidden email]>
Cc: David Marso <[hidden email]> <mailto:[hidden email]>
Sent: Sat, October 2, 2010 10:02:19 PM
Subject: Re: incomplete merge

Hey Matt,
Please post your SPSS code.
Difficult to do ESP on this one.
Respects, David

On Sat, 2 Oct 2010 17:25:32 -0700, Matthew Pirritano
<[hidden email]> wrote:

>All,
>
>I am having a most vexing problem.
>
>I have done many merges in my day. But for some reason this one is not
working.
>
>I have three files. A male file, a female file, and a full file that the
male
>and female files are being added to.
>
>I've placed the id field from each file that I am matching on next to each
other
>in excel and lo and behold they all match up. But when I run a match I wind
up
>with 145 missing fields. The fields are 10 character string fields. I do
matches
>like this on ssn in my sleep. What in the world could be going on here.
>
>
>Since the full file has both male and female data I'm doing a table look up
with
>the male or female file as the table. I actually don't think this should
matter,
>since the full file should match up with only one line each from the male
and
>female file. I've tried it without the table lookup and get the same
results.
>
>I've also tried to trim each field, thinking there might be some blanks.
That

>didn't help.
>
>
>Please someone help me see what I'm missing.
>
>Thanks
>Matt
>
> Matthew Pirritano, Ph.D.
>Email: [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

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