Matching Files in SPSS using Table or In

Question

I keep running into an error when trying to add variables of one spss file to another. File 1 has 1.800.000 cases [payments], File 2 has 800.000 cases [recipients]. They both have an ID number to match cases on.

For every payment in File 1 I want to add the recipient, from File 2. The recipients should thus be able to match for multiple payments.

This are the two codes I have been trying, which don't work:

code using IN

      DATASET ACTIVATE DataSet1. 
      SORT CASES BY recipientid(A).
      DATASET ACTIVATE DataSet2. 
      SORT CASES BY recipientid(A).

      Match Files /File=DataSet1
      /In=DataSet2
      /BY globalrecipientid.
      execute

When I use /In I don't get any errors, but the files don't properly match sin it doesn't add any variables.

code using TABLE

      DATASET ACTIVATE DataSet1. 
      SORT CASES BY recipientid(A).
      DATASET ACTIVATE DataSet2. 
      SORT CASES BY recipientid(A).

      Match Files /File=DataSet1
      /TABLE=DataSet2
      /BY globalrecipientid.
      execute

When I use /TABLE I get the following error:

Warning # 5132 Undefined error #5132 - Cannot open text file 'S:\Progra~1\spss\IBM\SPSS\STATIS~1\20\lang\en\spss.err": No such file or directory

I have run out of tricks, wouldn't dare try this in Ruby, and excel sadly is too small to handle this.. Any thoughts?

What happens if you use the build-in dialogue? Sometimes that produces somewhat different code, which works... — Christian Sauer
It seems you have a problem with SPSS installation. SPSS can not open the error message to be shown in the output. — djhurio

djhurio djhurio · Accepted Answer · 2013-03-14T16:00:05

Your first solution is wring because you are using IN subcommand wrongly. In other words you are matching Dataset1 with nothing.

IN creates a new variable in the resulting file that indicates whether a case came from the input file named on the preceding FILE subcommand.

Your second solution. You are sorting dataset by variable recipientid but the match files is done by the variable globalrecipientid. Why do you sort by one variable but match by another? This could be a problem. And dataset names should be in quotes.

Solution 1:

DATASET ACTIVATE DataSet1. 
SORT CASES BY recipientid (A).

DATASET ACTIVATE DataSet2. 
SORT CASES BY recipientid (A).

Match Files
 /File = "DataSet1"
 /TABLE = "DataSet2" 
 /BY recipientid.

execute.

Solution 2. I never liked the implementation of datasets in SPSS. I did not trusted them. Other solution is to save datasets as files and do the match of files.

get "file1.sav".
SORT CASES BY recipientid (A).
save out "file1s.sav".

get "file2.sav".
SORT CASES BY recipientid (A).
save out "file2s.sav".

Match Files
 /File = "file1s.sav"
 /TABLE = "file2s.sav"
 /BY recipientid.

execute.

Matching Files in SPSS using Table or In

code using IN

code using TABLE

2 Answers