This is easy to do using the point-and-click options in SPSS. I'll provide an example based on some files I just merged:

Dataset1 has 3275 cases

Dataset2 has 65 cases, 62 of which are also in Dataset1 and 3 of which are not

MERGING:

Open Dataset1. Sort ascending by ID. Save file.

Open Dataset2. Sort ascending by ID. Save file.

So now you have both files open. Click on Dataset1 so that it is the file in view (this will be the "Active Dataset"). Then select the following point-and-click options from the menu:

Data > Merge Files > Add Variables

Highlight Dataset2 in the Open Datasets and click Continue

Select ID from the list of Excluded Variables and move it into the Key Variables box after checking the following options:

"Match cases on key variables"

"Cases are sorted in order of key variables in both datasets"

Then you have 3 more options, and here's what they will give you:

If you select "Non-active dataset is keyed table," SPSS returns a file with the 3275 cases from Dataset1 and the new variable from Dataset2 for the 62 cases from Dataset2 who are also in Dataset1 and leaves the new variable blank for the rest of the cases. It does not add the 3 cases from Dataset2 who were not also in Dataset1.

If you select "Active dataset is keyed table," SPSS returns you a file with the 65 cases from Dataset2 and pulls in their variables from Dataset1, with blank values on those variables for the 3 cases who were in Dataset2 but not in Dataset1. It does not add the 3213 cases from Dataset1 who were not also in Dataset2.

If you select "Both files provide cases," it gives you a file with 3278 cases, including the 62 cases who were in both datasets, plus the 3213 cases from Dataset1 who were not also in Dataset2, plus the 3 cases from Dataset2 who were not also in Dataset1. And it pulls in the new variable from Dataset2 for the 65 cases with info and leaves this variable blank for the rest of the cases.

I hope one of those three options gives you what you are trying to achieve. Good luck!

Hi Liza,

you have to choose the appropriate /FILE and /TABLE options in MATCH.

All ids from a dataset specified as /FILE are in the resulting data set.

For example:

*generate example data sets.

DATA LIST /ID 1 MEASURE1 3-4.

BEGIN DATA

1 10

2 11

3 12

END DATA.

DATASET NAME set1.

DATA LIST /ID 1 MEASURE2 3-4.

BEGIN DATA

1 20

3 22

4 23

END DATA.

DATASET NAME set2.

DATASET ACTIVATE set1.

*make a copy.

dataset copy set3.

DATASET ACTIVATE set3.

*merge - result will have all ids from set2.

MATCH FILES /TABLE=*

/FILE='set2'

/BY id.

EXECUTE.

DATASET ACTIVATE set1.

*make a copy.

dataset copy set4.

DATASET ACTIVATE set4.

*result has all ids from either set1 or set2.

MATCH FILES /FILE=*

/FILE='set2'

/BY id.

EXECUTE.

hth.

Thank you Eik, I will try this syntax once we have finished editing the second dataset!

You need to choose a key variable that is the same in both data set. SPSS will match them and all the other variables will be added to the new data set.

An easy way to do this kind of merge would be to simply create a wave variable since it sounds like you are testing patients at various time points. Simply code this variable accordingly (wave 1 = 1, wave 2 = 2, wave 3 = 3, etc.) in each of the respective wave datasets, and make sure all other variables have the same name at each wave. Then simply do an add cases procedure:

ADD FILES /FILE=*

/FILE= 'Name_of_DataSet'.

EXECUTE.

The resultant database will have an unbalanced panel dataset structure, since you stated that cases will not be present in each wave.

If you do not prefer a panel data structure, but rather a wide file, use the Merge Files >> Add Variables procedure, and allow both files to provide cases. As long as the primary key is the same variable in both datasets, the merge statement should look like this:

MATCH FILES /FILE=*

/FILE= 'Name_of_DataSet'

/BY 'Name_of_Primary_Key'.

EXECUTE.

When using this procedure, the primary key should be the only duplicate variable name in the dataset, as each wave file should contain different variables names. An easy way to ensure this would be to simply append '_2' or '_3' to the variable names in each of the corresponding wave files.

Yeah Reza's approach is good. You can match based on the key variable and those which do not match will appear as missing on the matching variable be sure to add them.

Thank you all! I will try both options, as the second dataset (wave 3) has additional variables as well, so we need to add those.

Since you talking about waves, it sounds as if you are dealing with survey data. "Hamonization" might be the topic you are dealing with when variables do not match each other exactely.

For an into to harmonization:

http://ccsg.isr.umich.edu/harmonization.cfm

As an example for ex post variable harmonization you might want to look at the European Values Study (EVS):

http://www.gesis.org/en/services/data-analysis/survey-data/european-values-study/

This is a cross-cultural survey, but the troubles of harmonization variables are similar to longitudinal studies.

There is an online variable overview that let's you compare the original variable with the variables as coming from various countries. These country variables had to be recoded to put them together in ons integrated data file:

http://info1.gesis.org/EVS/Variables/

I think you have been advised enough. One wee thing - if using merge cases all cars including key identifier - ie case id?must match exactly I. Terms of string/ numerical length. Sometimes these differ in data sets & you won't be able to merge if they differ. Good luck!

You could also create a dummy id variable and merge on that field.

All worked, thank you!

I need much more detailed information about liza's question, shall anyone give.

First of all you have to sort your data in ascending or descending with reference to the key variable and then merge both files. SPSS will give you two options merge cases and merge variable. You have to use merge variable option. After that you have to mention key variables which should be in both files. You have the option to exclude some variable if you would like to otherwise SPSS will merge both files with all variables.

If you would like to add cases then use add cases option instead of add variable rest is same

The problem was solved. Merging is the most used technique.

Nathan,

Merging is usually utilized for adding some variables, not cases. We merge two files, those include different variable for a specific population with a common key variable, in order to have more variables for one population. It seems the 700 "re-arrested" are part of 1600. In this case, as Masood pointed, the first step is sorting records (data) in both data sets based on the ID number, as the key variable. If the ID is not unique for each case in both data set, you need to create another variable with unique value in the both data sets (for instance ID2), then merge them.

If you need to add cases, before everything, you should be sure that the names of the common variables are the same in both data sets.

So, when you are adding variables, just take the tick of "Match cases on key variable in sorted files". You will have some missing values for those records that are not in the smallest data set.

I think you should use this procedure (ass the SPSS help suggests):

To Merge Files with the Same Cases but Different (maybe new) Variables

Open at least one of the data files that you want to merge. If you have multiple datasets open, make one of the datasets that you want to merge the active dataset.

From the menus choose:

Data

Merge Files

Add Variables...

Select the dataset or external SPSS Statistics data file to merge with the active dataset.

To Select Key Variables

Select the variables from the external file variables (+) on the Excluded Variables list.

Select Match cases on key variables in sorted files.

Add the variables to the Key Variables list.

The key variables must exist in both the active dataset and the other dataset. Both datasets must be sorted by ascending order of the key variables, and the order of variables on the Key Variables list must be the same as their sort sequence.

I hope it helps.

I guess you cant do it by SPSS. You need to merge it with other programs. I had your problem too.

Learn R.

This CAN be done with SPSS, by following the steps outlined above by Roie.

Supposing that the common ID variable is named ID1, it must:

- Exist on both datasets (let's say the BaselineData is the active dataset, and FollowUpData is the non-active dataset, containing the Arrests2 variable(s))

- BOTH datasets must be sorted on ID1 (ascending); if you're not sure, just re-sort and save them both before the Merge procedure

Now, from BaselineData, when Merging to Add variables (e.g., Arrests2), select Match cases on key variables in sorted files. Either keep the default "Both files provide cases" (in which case Arrests2 will be missing for cases without a 2nd arrest; Drop these cases if needed); or, click "Non-active dataset is keyed table" (which will merge only IDs in the FollowUpData set).

Next, click (highlight) ID1 in the Excluded Variables box, to add it to the Key Variables list.

Finally, click OK (or Paste the code, then Run it), and verify your merged results.

One more tip: if you haven't done this previously, try it with 2 very small datasets (say, 5 cases, 2 variables), to troubleshoot your steps.

I hope this helps.

Good things i know from this discussion

If you're using SPSS Version 21, after clicking on 'Match cases on key variables' you also have to check 'Cases are sorted in order of key variables in both datasets.'

Something sounds crazy. Are active and non-active dataset considered reversely? I expect the active is the one which is now in the front page, but it works vice versa when merging.

I know it is not an exact solution to your SPSS problem but I would recommend using Microsoft Access to merge the files. It is easier than SPSS (and you can check that the results are right). Export your two tables into Access, then use a query to merge them (I recommend a 'create Table' query. Make a joint between your ID variables and change the joint properties (right-click on the joint) to include all cases of one file and only matching cases of the second file) and import the resulting table back in SPSS.

Both cases and variables have to be added if this is the case.

I had a problem very similar to Nathan's. I have a somewhat clunky work-around that I think worked for me. Here's what I did:

1) I had N=932 total sample records, with N=281 incident records (e.g., an incident report existed for 281 of the 932 individuals. The other 651 had no such record).

2) My initial attempts at merging the files by adding cases--using all sorts of different options--was not successful.

3) Instead, I manually copied and pasted the 932 ID numbers at the bottom of the smaller N=281 file.

4) I then ran Data-->Identify Duplicate Cases-->First Case is Primary and defined matching cases by the ID variable. This created PrimaryFirst, which was 1 for the first time the ID variable appeared and 0 for the later times the ID number was duplicated.

5) I then did Data-->Select Cases If-->PrimaryFirst=1 and deleted unselected cases. This made my dataset with N=281 incident records have a total of 932 ID numbers (with the lower 651 IDs having no information with them).

6) I re-sorted the dataset on ID to make sure they were in ascending order.

7) With both datasets having all 932 IDs in ascending order, I was able to successfully merge the files by adding variables (as detailed in previous posts).

This seems to have worked for me. I hope it works for you too!

The solution provided by Danielle is perfect. I tried it today on the dataset and it worked perfect for me. The previous post either deal with add cases or add variables seperately. The Danielle solution deals with both at the same time.

Thanks Danielle.

I'm glad it's working for other folks, too. You're very welcome! Good luck.

This is easy to do using the point-and-click options in SPSS. I'll provide an example based on some files I just merged:

Dataset1 has 3275 cases

Dataset2 has 65 cases, 62 of which are also in Dataset1 and 3 of which are not

MERGING:

Open Dataset1. Sort ascending by ID. Save file.

Open Dataset2. Sort ascending by ID. Save file.

So now you have both files open. Click on Dataset1 so that it is the file in view (this will be the "Active Dataset"). Then select the following point-and-click options from the menu:

Data > Merge Files > Add Variables

Highlight Dataset2 in the Open Datasets and click Continue

Select ID from the list of Excluded Variables and move it into the Key Variables box after checking the following options:

"Match cases on key variables"

"Cases are sorted in order of key variables in both datasets"

Then you have 3 more options, and here's what they will give you:

If you select "Non-active dataset is keyed table," SPSS returns a file with the 3275 cases from Dataset1 and the new variable from Dataset2 for the 62 cases from Dataset2 who are also in Dataset1 and leaves the new variable blank for the rest of the cases. It does not add the 3 cases from Dataset2 who were not also in Dataset1.

If you select "Active dataset is keyed table," SPSS returns you a file with the 65 cases from Dataset2 and pulls in their variables from Dataset1, with blank values on those variables for the 3 cases who were in Dataset2 but not in Dataset1. It does not add the 3213 cases from Dataset1 who were not also in Dataset2.

If you select "Both files provide cases," it gives you a file with 3278 cases, including the 62 cases who were in both datasets, plus the 3213 cases from Dataset1 who were not also in Dataset2, plus the 3 cases from Dataset2 who were not also in Dataset1. And it pulls in the new variable from Dataset2 for the 65 cases with info and leaves this variable blank for the rest of the cases.

I hope one of those three options gives you what you are trying to achieve. Good luck!

I tried the method suggested by Jennifer and it worked perfectly as well, and more systematic, which is fantastic.

Thanks Jennifer for the time to share your skills.

I have a similar problem. I cannot seem to add them together because in Wave 1, I have about 8,000 participants, 420 in wave 2, and 350 in wave 3. I want to merge them, but the point and click method isn't working as a message saying "variables are of different lengths" keeps appearing and will not let me proceed. Does anyone know what I should do?

Open the variable (the one that is used as the ID variable) and make sure this variable in all files has the same length. The length here is actually the width of the variable. You can see that in the "Variable View", then look at the "Width" column. You can see 8 or 12 or 22. Make them the same and problem solved.

Thank you so much T! That was so helpful, and now my data is finally sorted :)

My pleasure Jessica. I am glad I can help.

Cheers,

Thu Vuong

May I ask a similar question here? I am trying to merge two files but I'm having a problem matching the data by the key variable because the variable is repetitive within another variable like:

Country  ID

1  1

1  2

1  3

2  1

2  2

2  3

Since the key variable (ID) repeats its self, SPSS doesn't know which to match:

Duplicate key in a file. The BY variables do not uniquely identify each case on the indicated file.

Is there anyway I can merge by ID but just for the country variable when it is 1 (ignoring country 2 and others.)? I know I can manually delete it, but since the data are from PISA database and there are over thousands of them. It would be very helpful if anyone can help me with this problem.

Thank you.

Similar problem here. I have a large database with subjects that have checkups on multiple occasions, I am trying to insert an other data file with the same subjects but some subjects do not have the same number checkups.

The first row of my original data file has the Subject ID and the second the number of the checkup. 

Original Database                                New Database

ID  Checkup                                           ID    Checkup

1   1                                                          1     1

1  2                                                           1     2

1  3                                                           1     3

1  4                                                           1     4

1  5                                                           2     2

2  1                                                           2     3

2  2                                                           2     4         

2  3                                                           2     5

2  4                                                           3     1

etc.

The other data shares the ID number but Checkup is different. Some subjects missed the first checkup or the last etc. How can I merge the data based on the two variables ID and checkup and have empty values if data does not match with the old database? 

My problem is in this group but slightly different:  When I use Jennifer's excellent method, above, SPSS stops merging the new variable as soon as there is missing data in the record, which means it stops at record 50 of 4676!  Anyway to tell SPSS how to handle missing data when merging files?

I have one piece of advice to add to Jennifer's excellent method: set the width of the key variable(s) to be identical in the two files.

I have not had the problem with SPSS handling missing cases.

this video shows step by step how to do it:

https://www.youtube.com/watch?v=unB1pI2WaIo

👍🏻👍🏻

The method of merging has been well described by Jennifer Maxfield . Please try now and come back if you face any difficulty. One more thing in addition to checking all the characteristics of the variable that has been mentioned by Amy Eshleman, you should select the unique ID to filter and merge the two data set in the merge dialog box.

Does anyone know how to use this method with version 25 of SPSS? I am trying to merge two datasets that have different variables and cases (some variables are in both datasets - ID) but with version 25 I am not able to choose the "Both files provide cases", it automatically clicks "active dataset" w/out an option to change this.

Thanks Danielle and Jennifer, this is exactly what I was looking for. However ,can this be done in LISREL because I want to estimate a model from 2 different cases (120 and 300) ?