|
Size: 2263
Comment:
|
← Revision 14 as of 2023-06-09 21:03:49 ⇥
Size: 889
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 3: | Line 3: |
| Firstly the SPSS commands are listed with an explanation of syntax. Secondly the equivalents of standard SQL joins are listed. | SPSS offers several commands for joining datasets. |
| Line 11: | Line 11: |
| == Match Files == | == Full Join == |
| Line 13: | Line 13: |
| The '''`MATCH FILE`''' command is ''primarily'' used for 1:1 joins, where all cases are uniquely identified in all datasets. Try: | The primary method for any join in SPSS is the [[SPSS/MatchFiles|MATCH FILES]] command. |
| Line 22: | Line 22: |
| The final dataset contains all rows and variables from all datasets. Variables are taken in order from the datasets in order. For variables originating from more than one dataset, values are taken from the first dataset they appear in and metadata is taken from the first dataset with any (i.e. variable label, value labels, or missing values) metadata set. For more details on the `MATCH FILES` command, see [[SPSS/MatchFiles|here]]. |
If the datasets are known to have no overlap, the [[SPSS/AddFiles|ADD FILES]] command is also an option. |
| Line 30: | Line 28: |
| == Update File == The '''`UPDATE FILE`''' command is used to overwrite values in a master file with non-null values in transaction files. Try: |
== Left Join == |
| Line 35: | Line 31: |
| update file=LEFT /file=TRANSACTION |
match files /file=LEFT /in=keep /file=RIGHT |
| Line 38: | Line 35: |
| select if keep=1. | |
| Line 40: | Line 38: |
| The final dataset contains all rows and variables from all datasets. Rows and variables originating from a transaction file are appended. For more details on the `UPDATE FILE` command, see [[SPSS/UpdateFile|here]]. |
The [[SPSS/StarJoin|STAR JOIN]] command is also available. |
| Line 48: | Line 44: |
| == Star Join == | == Right Join == |
| Line 50: | Line 46: |
| The final dataset contains only the variables specified on the `SELECT` and `JOIN` subcommand, and only the rows originating from the case file. Note: unsupported in SPSS version 20 or earlier. |
{{{ match files /file=LEFT /file=RIGHT /in=keep /by ID. select if keep=1. }}} |
| Line 58: | Line 58: |
| == Joins == === Full Join === |
== Inner Join == |
| Line 63: | Line 61: |
| dataset activate LEFT. | |
| Line 65: | Line 62: |
| /file=* /file=RIGHT |
/file=LEFT /in=left /file=RIGHT /in=right |
| Line 68: | Line 65: |
| execute. }}} === Left Join === {{{ dataset activate LEFT. match files /file=* /in=flag_left /file=RIGHT /in=flag_right /by ID. select if (flag_left=1). execute. }}} === Right Join === {{{ dataset activate LEFT. match files /file=* /in=flag_left /file=RIGHT /in=flag_right /by ID. select if (flag_right=1). execute. }}} === Inner Join === {{{ dataset activate LEFT. match files /file=* /in=flag_left /file=RIGHT /in=flag_right /by ID. select if (flag_left=1 and flag_right=1). execute. |
select if left=1 and right=1. |
Joining Data with SPSS
SPSS offers several commands for joining datasets.
Full Join
The primary method for any join in SPSS is the MATCH FILES command.
match files /file=LEFT /file=RIGHT /by ID.
If the datasets are known to have no overlap, the ADD FILES command is also an option.
Left Join
match files /file=LEFT /in=keep /file=RIGHT /by ID. select if keep=1.
The STAR JOIN command is also available.
Right Join
match files /file=LEFT /file=RIGHT /in=keep /by ID. select if keep=1.
Inner Join
match files /file=LEFT /in=left /file=RIGHT /in=right /by ID. select if left=1 and right=1.