Losing Cases in One to Many Merge with missing data

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

jvanneste
Posts: 3
Joined: Thu Oct 27, 2016 7:15 pm

Losing Cases in One to Many Merge with missing data

Postby jvanneste » Thu Oct 27, 2016 7:49 pm

I have been struggling all day to merge data (which I do very frequently), but every time I lose data from the master file.

The merge has to be match on both the SSN and Reporting Month Variable
File A: This is the master file and I need to keep all rows, no data can be lost, it is unduplicated.
File B: this file has duplicates on a "SSN" variable (several clients have a 0 instead of a SSN), I need to merge this data into File A if File A has the same SSN. If only File B has a SSN then the data can be lost
File A: must be the keyed since File B is not unduplicated. Both files have a few unique SSNs in each that are not in the other file.
Both files are presorted by SSN and ReportMonth (matching variables)

I have tried both working through the the wizard and syntax and can not remedy the problem.
Both setting:
File A are the original joined to file B, and A as the key
File B as the original file joined to file A, and file A as the key.
And I have tried with and without clicking the "cases are sorted in order of keyed variable in both files" with changes it from a match files join to a star join. same result.

In all of these versions I am missing cases from File A after the join. And I have rows completed from File B with no File A data. I.E. no matter what is is keep all B cases and merging in A only if B has a matching SSN and Month.

I can not do any manual coding in this process as there are over 80,000 rows and this is a monthly report.
jvanneste
Posts: 3
Joined: Thu Oct 27, 2016 7:15 pm

Re: Losing Cases in One to Many Merge with missing data

Postby jvanneste » Mon Nov 07, 2016 2:01 pm

Does anyone have advise on this. Or Do I need to explain better? Or advise on where i may go to get an answer
statman
Administrator
Posts: 2750
Joined: Tue Jun 12, 2007 12:08 pm
Location: Florida, USA

Re: Losing Cases in One to Many Merge with missing data

Postby statman » Mon Nov 07, 2016 2:15 pm

need info on the data type(s) so ................
See the note below

NOTE: Please read the Posting Guidelines and always tell us your OS, the SPSS version and information about your study and data!

Statman
Statistical Services
jvanneste
Posts: 3
Joined: Thu Oct 27, 2016 7:15 pm

Re: Losing Cases in One to Many Merge with missing data

Postby jvanneste » Tue Nov 08, 2016 5:16 pm

I tried creating a simplified dataset to explore. The first dataset must keep all cases (notice case 4 does not appear in dataset 2, and dataset 2 has case 5, which does not appear in dataset 1). However in the various ways i have tried it, I am loosing some cases from Dataset 1 (case 4)

Case Date Color
1 1/1/2016 red
1 2/1/2016 blu
2 1/1/2016 gre
2 2/1/2016 pur
3 1/1/2016 gre
3 2/1/2016 red
4 1/1/2016 pur
4 2/1/2016 gre
6 1/1/2016 red
6 2/1/2016 blu
7 1/1/2016 red
7 2/1/2016 blu
8 1/1/2016 gre
8 2/1/2016 pur
9 1/1/2016 gre
9 2/1/2016 red
10 1/1/2016 pur
10 2/1/2016 gre

case Date fruit
1 1/1/2016 orange
1 2/1/2016 apple
2 1/1/2016 apple
2 2/1/2016 apple
3 1/1/2016 banana
3 2/1/2016 orange
5 1/1/2016 orange
5 2/1/2016 banana
6 1/1/2016 cherry
6 1/1/2016 orange
6 2/1/2016 grape
6 2/1/2016 orange
7 1/1/2016 orange
7 2/1/2016 kiwi
8 1/1/2016 apple
8 1/1/2016 cherry
8 2/1/2016 cherry
8 2/1/2016 kiwi
9 1/1/2016 banana
9 2/1/2016 orange
10 1/1/2016 grape
10 2/1/2016 apple
SimonHet
Posts: 3
Joined: Mon Jul 17, 2017 9:03 pm

Losing Cases in One to Many Merge with missing data

Postby SimonHet » Thu Aug 10, 2017 9:07 pm

Between us speaking, I would address for the help to a moderator.
world to world

Who is online

Users browsing this forum: No registered users and 2 guests

cron