How can I merge three datasets, some of which are incomplete, into one, based on a shared unique value in one field?

Question

I will be using qualtrics to deliver three surveys to children. So far the first survey uses the function ${e//Field/Login%20ID to provide the user with their 10 digit randomised code. They then require that to access the follow up surveys. Pilot testing showed that no one remembered their code.

We had an idea of alternatively asking them to generate their own code that could be easily re-generated, then ask that as a question in each survey as a way of identifying one person’s record across each one. All participants will do survey 1 and 2, only some will do survey 3.

The code will be something like SchoolName-YearGroup-FirstInitial-DayOfBirth. We are unlikely to get duplicates given our predicted sample size.

The problem is Qualtrics says there is no way to merge records based on all having the same answer in the first question field. The dataset is too large to do this manually. Any ideas? I have used SPSS but not R or Stata. I am willing to learn to the best of my ability in order to manage this issue!

I called Qualtrics and they said they can’t do it in Qualtrics unless I have each person’s email and send them an individualised code to link their data but we won’t be asking for emails at all due to restrictions on our methods. I have tried using SPSS with a sample data set and attempting to merge using

MATCH FILES
  /FILE='path_to_survey2.sav'
  /BY ParticipantCode.
EXECUTE.

But this didn’t do anything.

Answer 1

If you have a ParticipantCode in the two files and you have survey 1 open and as active dataset, what you want to do to match the two is:

dataset name survey1.
sort cases by ParticipantCode.
get file='path_to_survey2.sav'.
sort cases by ParticipantCode.
dataset name survey2.
dataset activate survey1.
MATCH FILES /FILE=* /table=survey2 /BY ParticipantCode. 
EXECUTE.