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