The SPSS command star join was added in version 21. Like the match files command, the star join command is used to merge data files. Below is a short list of the similarities and differences between these two commands.
Similarities:
- Multiple SPSS datasets can be merged at a time.
- Not all variables from the datasets to be merged need to be included in the new, merged dataset.
- An in keyword or subcommand is available to create a new variable in the merged data file that indicates from which file each case came.
- Neither the match files nor the star join command will do a many-to-many merge.
Differences:
- The datasets do not have to be sorted on the variable(s) that will be used to merge the files.
- String variables can be of different lengths with the star join command, but not with the match files command.
- Variables can be renamed within the match files command but not within the star join command.
- The variable on which cases are matched does not need to have the same name in the datasets using the star join command.
- Binary variables indicating the first or last case in a grouping can be created with the match files command but not with the star join command.
- The star join command can be used to password protect the resulting file.
- The data file resulting from the match cases command becomes the active dataset; the data file resulting from the star join command does not.
- The match files command has a limit of 50 datasets that can be merged in one call to the command; the limit for the number of datasets that can be merged using the star join command is set by the operating system (in other words, there is no set limit to the number of datasets that can be merged with one call to star join).
- There is no in keyword on the from subcommand (meaning that you don’t get a binary variable indicating if a case came from that dataset or not).
The hsbdemo , hsb_school and hsb_tests1 datasets will be used in the following examples.
Subcommands
The select subcommand is used to determine which variables from each dataset will be included in the final, merged dataset. All of the specifications on this subcommand should be of the general form “alias.variable_name”, where “alias” is the name given to each dataset on the from and join subcommands. The entries on this subcommand must be separated by commas. The select subcommand must be the first subcommand listed, and there can only be one select subcommand in the call to the procedure. An asterisk (*) can be used to specify all variables (except the key, or identifying variable) in a dataset.
The from subcommand must immediately follow the select subcommand and specifies the name of the case data file. The keyword as is also necessary, and it is followed by the alias used to name/identify that dataset. External datasets must be enclosed in quotes. Only one from subcommand is allowed.
The table lookup file is specified on the join subcommand. After the name of the file, the keyword as is used, and then the alias of the dataset is given. At least one join subcommand is required, but many join subcommands can be specified. The maximum number of join subcommands permitted is determined by the operating system. Like the from subcommand, external datasets must be listed in quotation marks. An asterisk can be used to specify a dataset that is already open in SPSS. The values in the key (or identifying) variable must be unique. If multiple variables are used to identify cases in the dataset, the combination of values must be unique. The on keyword is used to name the keys used to match cases. The variable type (either string or numeric) must be the same in all files, but the names of the variable (or variables) do not have to be the same. The in keyword is optional and can be used to create a binary variable that indicates if the case came from that dataset (1 = present, 0 = not present).
On the outfile subcommand is the name of the new, merged dataset. The keyword file is necessary and must be followed by an equals (=) sign. An external dataset (which must be in quotes), a previously declared dataset (need to use the dataset declare command before the star join command) or an asterisk (indicating the currently active dataset) may be specified.
Additionally, the passprotect subcommand can be used to save the resulting, merged file in encrypted format. An external file must be specified on the outfile subcommand if the passprotect subcommand is used. The password keyword is necessary, and the password can be up to 10 characters long. The password itself can be encrypted using encryptedpw = yes.
Example 1: A one-to-one merge with two datasets creating an external dataset
In this example, we will use the star join command to do a one-to-one merge with two SPSS datasets. A one-to-one merge matches one case in one dataset to one case in the other dataset. This matching is done based on a specified variable or variables that uniquely identifies each case in the dataset. In our example, we are matching cases in the hsb_demo dataset (with the alias d1) to cases in the hsb_school dataset (with the alias d2) based on the values in the variable id. The variable id is present in both datasets, and it is a unique identifier of cases in each dataset. The external dataset demo_school.sav contains the merged dataset.
get file "D:\data\hsbdemo.sav". star join /select d1.female d1.ses, d2.prog d2.schtyp /from "D:\data\hsbdemo.sav" as d1 /join "D:\data\hsb_school.sav" as d2 on d1.id = d2.id /outfile file = "D:\data\demo_school.sav". get file "D:\data\demo_school.sav". list /cases from 1 to 10.id female ses prog schtyp 1.00 1.00 1.00 3.00 1.00 2.00 1.00 2.00 3.00 1.00 3.00 .00 1.00 2.00 1.00 4.00 1.00 1.00 2.00 1.00 5.00 .00 1.00 2.00 1.00 6.00 1.00 1.00 2.00 1.00 7.00 .00 2.00 2.00 1.00 8.00 1.00 1.00 2.00 1.00 9.00 .00 2.00 3.00 1.00 10.00 1.00 2.00 1.00 1.00 Number of cases read: 10 Number of cases listed: 10
Now let’s look at the syntax that does the same thing using the match files command.
get file "D:\data\hsbdemo.sav". sort cases by id. save outfile = "D:\data\hsbdemo.sav". get file "D:\data\hsb_school.sav". sort cases by id. save outfile = "D:\data\hsb_school.sav". match files file = "D:\data\hsb_demo.sav" /file = "D:\data\hsb_school.sav" /by id. list /cases from 1 to 10.id female ses race prog schtyp 1.00 1.00 1.00 1.00 3.00 1.00 2.00 1.00 2.00 1.00 3.00 1.00 3.00 .00 1.00 1.00 2.00 1.00 4.00 1.00 1.00 1.00 2.00 1.00 5.00 .00 1.00 1.00 2.00 1.00 6.00 1.00 1.00 1.00 2.00 1.00 7.00 .00 2.00 1.00 2.00 1.00 8.00 1.00 1.00 1.00 2.00 1.00 9.00 .00 2.00 1.00 3.00 1.00 10.00 1.00 2.00 1.00 1.00 1.00 Number of cases read: 10 Number of cases listed: 10
As you can see, we need to open each dataset (using the get file command), sort it (using the sort cases command) and save the file (using the save command). Once we have done that for each dataset we wish to merge, we can use the match files command to merge the files. (The list command is not a necessary part of this process; we use it here only to display the resulting output.)
Example 2: A one-to-one merge with three datasets creating an external dataset
In this example, we merge three datasets, hsbdemo, hsb_school and hsb_tests1. Note that the identification variable is called id in hsbdemo and hsb_school, but it is called sid in hsb_tests1. Also, we have added two new variables, called first and second, to our new, merged dataset. We did this with the in keyword on the join subcommand. These variables will have a value of 1 when the case comes from that dataset and a 0 when it does not.
star join /select d1.female d1.ses, d2.prog d2.schtyp, d3.read d3.write /from "D:\data\hsbdemo.sav" as d1 /join "D:\data\hsb_school.sav" as d2 on d1.id = d2.id in=first /join "D:\data\hsb_tests1.sav" as d3 on d1.id = d3.sid in = second /outfile file = "D:\data\demo_school_test1.sav". get file "D:\data\demo_school_test1.sav". list /cases from 1 to 10.id female ses prog schtyp read write first second 1.00 1.00 1.00 3.00 1.00 34.00 44.00 1 1 2.00 1.00 2.00 3.00 1.00 39.00 41.00 1 1 3.00 .00 1.00 2.00 1.00 63.00 65.00 1 1 4.00 1.00 1.00 2.00 1.00 44.00 50.00 1 1 5.00 .00 1.00 2.00 1.00 47.00 40.00 1 1 6.00 1.00 1.00 2.00 1.00 47.00 41.00 1 1 7.00 .00 2.00 2.00 1.00 57.00 54.00 1 1 8.00 1.00 1.00 2.00 1.00 39.00 44.00 1 1 9.00 .00 2.00 3.00 1.00 48.00 49.00 1 1 10.00 1.00 2.00 1.00 1.00 47.00 54.00 1 1 Number of cases read: 10 Number of cases listed: 10
To use the match files command, we will need to open, sort and save each of the three datasets. We will also have to rename sid to id, so that the identification variable has the same name in each of the datasets. If we wanted to limed the number of variables in the resulting, merged dataset as we did using the star join command, we would need to use the keep subcommand on the save command to keep only those variables in the dataset.
get file "D:\data\hsbdemo.sav". sort cases by id. save outfile = "D:\data\hsbdemo_sorted.sav" /keep = id female ses. get file "D:\data\hsb_school.sav". sort cases by id. save outfile = "D:\data\hsb_school_sorted.sav". get file "D:\data\hsb_tests1.sav". rename variables sid = id. sort cases by id. save outfile = "D:\data\hsb_tests1_sorted.sav" /keep = id read write. match files file = "D:\data\hsb_demo_sorted.sav" /in = demo /file = "D:\data\hsb_school_sorted.sav" /in = school /file = "D:\data\hsb_tests1_sorted.sav" /in = test1 /by id. list /cases from 1 to 10.id female ses prog schtyp read write demo school test1 1.00 1.00 1.00 3.00 1.00 34.00 44.00 1 1 1 2.00 1.00 2.00 3.00 1.00 39.00 41.00 1 1 1 3.00 .00 1.00 2.00 1.00 63.00 65.00 1 1 1 4.00 1.00 1.00 2.00 1.00 44.00 50.00 1 1 1 5.00 .00 1.00 2.00 1.00 47.00 40.00 1 1 1 6.00 1.00 1.00 2.00 1.00 47.00 41.00 1 1 1 7.00 .00 2.00 2.00 1.00 57.00 54.00 1 1 1 8.00 1.00 1.00 2.00 1.00 39.00 44.00 1 1 1 9.00 .00 2.00 3.00 1.00 48.00 49.00 1 1 1 10.00 1.00 2.00 1.00 1.00 47.00 54.00 1 1 1 Number of cases read: 10 Number of cases listed: 10
Example 3: A one-to-many merge creating an active dataset
For this example, we will input two small datasets. Next, we need to name and declare the dataset (using the dataset name and dataset declare commands) we are creating in the star join command. This gives a name to a dataset that is not yet open or created. In our example, we use the name one_many for our new dataset.
data list list /id test1 test2. begin data. 1 50 80 2 55 88 3 44 77 4 66 33 end data. dataset name one. data list list /id test3 test4. begin data. 1 11 22 2 22 11 2 55 66 3 44 55 3 44 33 3 66 77 4 88 99 4 77 22 4 11 55 4 77 88 end data. dataset name many. dataset declare one_many. star join /select m2.test1 m2.test2, m1.test3 m1.test4 /from many as m1 /join one as m2 on m1.id = m2.id /outfile file = one_many. dataset activate one_many. list.id test1 test2 test3 test4 1.00 50.00 80.00 11.00 22.00 2.00 55.00 88.00 22.00 11.00 2.00 55.00 88.00 55.00 66.00 3.00 44.00 77.00 44.00 55.00 3.00 44.00 77.00 44.00 33.00 3.00 44.00 77.00 66.00 77.00 4.00 66.00 33.00 88.00 99.00 4.00 66.00 33.00 77.00 22.00 4.00 66.00 33.00 11.00 55.00 4.00 66.00 33.00 77.00 88.00 Number of cases read: 10 Number of cases listed: 10
Example 4: Multiple identifying variables
In this example, we use two identifying variables. In other words, we need two variables to uniquely identify each case. Additionally, these variables are string variables with different lengths: the variable id1 has a length of three in the x_data dataset and a length of four in the y_data dataset. The variable id2 has a length of two in the x_data dataset and a length of three in the y_data dataset.
data list /id1 1-3 (A) id2 5-7 (A) x 9. begin data. a1a b1b 5 c2c d2d 6 a1a d2d 7 c2c e3e 8 end data. dataset name x_data. data list /id1 1-4 (A) id2 6-9 (A) y 11. begin data. a1a b1b 1 c2c d2d 2 a1a d2d 3 c2c e3e 4 end data. dataset name y_data. dataset declare x_y. star join /select data1.x , data2.y /from x_data as data1 /join y_data as data2 on data1.id1 = data2.id1 and data1.id2 = data2.id2 /outfile file = x_y. dataset activate x_y. list.id1 id2 x y a1a b1b 5 1 a1a d2d 7 3 c2c d2d 6 2 c2c e3e 8 4 Number of cases read: 4 Number of cases listed: 4