1 @node Combining Data Files
2 @chapter Combining Data Files
4 This chapter describes commands that allow data from system files,
5 portable files, and open datasets to be combined to
6 form a new active dataset. These commands can combine data files in the
11 @cmd{ADD FILES} interleaves or appends the cases from each input file.
12 It is used with input files that have variables in common, but
13 distinct sets of cases.
16 @cmd{MATCH FILES} adds the data together in cases that match across
17 multiple input files. It is used with input files that have cases in
18 common, but different information about each case.
21 @cmd{UPDATE} updates a master data file from data in a set of
22 transaction files. Each case in a transaction data file modifies a
23 matching case in the primary data file, or it adds a new case if no
24 matching case can be found.
27 These commands share the majority of their syntax, which is described
28 in the following section, followed by one section for each command
29 that describes its specific syntax and semantics.
32 * Combining Files Common Syntax::
33 * ADD FILES:: Interleave cases from multiple files.
34 * MATCH FILES:: Merge cases from multiple files.
35 * UPDATE:: Update cases using transactional data.
38 @node Combining Files Common Syntax
39 @section Common Syntax
43 /FILE=@{*,'@var{file_name}'@}
44 [/RENAME=(@var{src_names}=@var{target_names})@dots{}]
49 /BY @var{var_list}[(@{D|A@})] [@var{var_list}[(@{D|A@}]]@dots{}
50 [/DROP=@var{var_list}]
51 [/KEEP=@var{var_list}]
52 [/FIRST=@var{var_name}]
53 [/LAST=@var{var_name}]
57 This section describes the syntactical features in common among the
58 @cmd{ADD FILES}, @cmd{MATCH FILES}, and @cmd{UPDATE} commands. The
59 following sections describe details specific to each command.
61 Each of these commands reads two or more input files and combines them.
62 The command's output becomes the new active dataset.
63 None of the commands actually change the input files.
64 Therefore, if you want the changes to become permanent, you must explicitly
65 save them using an appropriate procedure or transformation (@pxref{System and Portable File IO}).
67 The syntax of each command begins with a specification of the files to
68 be read as input. For each input file, specify FILE with a system
69 file or portable file's name as a string, a dataset (@pxref{Datasets})
70 or file handle name, (@pxref{File Handles}), or an asterisk (@samp{*})
71 to use the active dataset as input. Use of portable files on @subcmd{FILE} is a
74 At least two @subcmd{FILE} subcommands must be specified. If the active dataset
75 is used as an input source, then @cmd{TEMPORARY} must not be in
78 Each @subcmd{FILE} subcommand may be followed by any number of @subcmd{RENAME}
79 subcommands that specify a parenthesized group or groups of variable
80 names as they appear in the input file, followed by those variables'
81 new names, separated by an equals sign (@subcmd{=}),
82 e.g. @subcmd{/RENAME=(OLD1=NEW1)(OLD2=NEW2)}. To rename a single
83 variable, the parentheses may be omitted: @subcmd{/RENAME=@var{old}=@var{new}}.
84 Within a parenthesized group, variables are renamed simultaneously, so
85 that @subcmd{/RENAME=(@var{A} @var{B}=@var{B} @var{A})} exchanges the
86 names of variables @var{A} and @var{B}.
87 Otherwise, renaming occurs in left-to-right order.
89 Each @subcmd{FILE} subcommand may optionally be followed by a single @subcmd{IN}
90 subcommand, which creates a numeric variable with the specified name
91 and format F1.0. The IN variable takes value 1 in an output case if
92 the given input file contributed to that output case, and 0 otherwise.
93 The @subcmd{DROP}, @subcmd{KEEP}, and @subcmd{RENAME} subcommands have no effect on IN variables.
95 If @subcmd{BY} is used (see below), the @subcmd{SORT} keyword must be specified after a
96 @subcmd{FILE} if that input file is not already sorted on the @subcmd{BY} variables.
97 When @subcmd{SORT} is specified, @pspp{} sorts the input file's data on the @subcmd{BY}
98 variables before it applies it to the command. When @subcmd{SORT} is used, @subcmd{BY}
99 is required. @subcmd{SORT} is a @pspp{} extension.
101 @pspp{} merges the dictionaries of all of the input files to form the
102 dictionary of the new active dataset, like so:
106 The variables in the new active dataset are the union of all the input files'
107 variables, matched based on their name. When a single input file
108 contains a variable with a given name, the output file will contain
109 exactly that variable. When more than one input file contains a
110 variable with a given name, those variables must all have the same
111 type (numeric or string) and, for string variables, the same width.
112 Variables are matched after renaming with the @subcmd{RENAME} subcommand.
113 Thus, @subcmd{RENAME} can be used to resolve conflicts.
116 The variable label for each output variable is taken from the first
117 specified input file that has a variable label for that variable, and
118 similarly for value labels and missing values.
121 The file label of the new active dataset (@pxref{FILE LABEL}) is that of the
122 first specified @subcmd{FILE} that has a file label.
125 The documents in the new active dataset (@pxref{DOCUMENT}) are the
126 concatenation of all the input files' documents, in the order in which
127 the @subcmd{FILE} subcommands are specified.
130 If all of the input files are weighted on the same variable, then the
131 new active dataset is weighted on that variable. Otherwise, the new
132 active dataset is not weighted.
135 The remaining subcommands apply to the output file as a whole, rather
136 than to individual input files. They must be specified at the end of
137 the command specification, following all of the @subcmd{FILE} and related
138 subcommands. The most important of these subcommands is @subcmd{BY}, which
139 specifies a set of one or more variables that may be used to find
140 corresponding cases in each of the input files. The variables
141 specified on @subcmd{BY} must be present in all of the input files.
142 Furthermore, if any of the input files are not sorted on the @subcmd{BY}
143 variables, then @subcmd{SORT} must be specified for those input files.
145 The variables listed on @subcmd{BY} may include (A) or (D) annotations to
146 specify ascending or descending sort order. @xref{SORT CASES}, for
147 more details on this notation. Adding (A) or (D) to the @subcmd{BY} subcommand
148 specification is a @pspp{} extension.
150 The @subcmd{DROP} subcommand can be used to specify a list of variables to
151 exclude from the output. By contrast, the @subcmd{KEEP} subcommand can be used
152 to specify variables to include in the output; all variables not
153 listed are dropped. @subcmd{DROP} and @subcmd{KEEP} are executed in left-to-right order
154 and may be repeated any number of times. @subcmd{DROP} and @subcmd{KEEP} do not affect
155 variables created by the @subcmd{IN}, @subcmd{FIRST}, and @subcmd{LAST} subcommands, which are
156 always included in the new active dataset, but they can be used to drop
157 @subcmd{BY} variables.
159 The @subcmd{FIRST} and @subcmd{LAST} subcommands are optional. They may only be
160 specified on @cmd{MATCH FILES} and @cmd{ADD FILES}, and only when @subcmd{BY}
161 is used. @subcmd{FIRST} and @subcmd{LIST} each adds a numeric variable to the new
162 active dataset, with the name given as the subcommand's argument and F1.0
163 print and write formats. The value of the @subcmd{FIRST} variable is 1 in the
164 first output case with a given set of values for the @subcmd{BY} variables, and
165 0 in other cases. Similarly, the @subcmd{LAST} variable is 1 in the last case
166 with a given of @subcmd{BY} values, and 0 in other cases.
168 When any of these commands creates an output case, variables that are
169 only in files that are not present for the current case are set to the
170 system-missing value for numeric variables or spaces for string
173 These commands may combine any number of files, limited only by the
184 /FILE=@{*,'@var{file_name}'@}
185 [/RENAME=(@var{src_names}=@var{target_names})@dots{}]
190 [/BY @var{var_list}[(@{D|A@})] [@var{var_list}[(@{D|A@})]@dots{}]]
191 [/DROP=@var{var_list}]
192 [/KEEP=@var{var_list}]
193 [/FIRST=@var{var_name}]
194 [/LAST=@var{var_name}]
198 @cmd{ADD FILES} adds cases from multiple input files. The output,
199 which replaces the active dataset, consists all of the cases in all of
202 @subcmd{ADD FILES} shares the bulk of its syntax with other @pspp{} commands for
203 combining multiple data files. @xref{Combining Files Common Syntax},
204 above, for an explanation of this common syntax.
206 When @subcmd{BY} is not used, the output of @subcmd{ADD FILES} consists of all the cases
207 from the first input file specified, followed by all the cases from
208 the second file specified, and so on. When @subcmd{BY} is used, the output is
209 additionally sorted on the @subcmd{BY} variables.
211 When @subcmd{ADD FILES} creates an output case, variables that are not part of
212 the input file from which the case was drawn are set to the
213 system-missing value for numeric variables or spaces for string
224 /@{FILE,TABLE@}=@{*,'@var{file_name}'@}
225 [/RENAME=(@var{src_names}=@var{target_names})@dots{}]
230 /BY @var{var_list}[(@{D|A@}] [@var{var_list}[(@{D|A@})]@dots{}]
231 [/DROP=@var{var_list}]
232 [/KEEP=@var{var_list}]
233 [/FIRST=@var{var_name}]
234 [/LAST=@var{var_name}]
238 @cmd{MATCH FILES} merges sets of corresponding cases in multiple
239 input files into single cases in the output, combining their data.
241 @cmd{MATCH FILES} shares the bulk of its syntax with other @pspp{} commands for
242 combining multiple data files. @xref{Combining Files Common Syntax},
243 above, for an explanation of this common syntax.
245 How @cmd{MATCH FILES} matches up cases from the input files depends on
246 whether @subcmd{BY} is specified:
250 If @subcmd{BY} is not used, @cmd{MATCH FILES} combines the first case from each input
251 file to produce the first output case, then the second case from each
252 input file for the second output case, and so on. If some input files
253 have fewer cases than others, then the shorter files do not contribute
254 to cases output after their input has been exhausted.
257 If @subcmd{BY} is used, @cmd{MATCH FILES} combines cases from each input file that
258 have identical values for the @subcmd{BY} variables.
260 When @subcmd{BY} is used, @subcmd{TABLE} subcommands may be used to introduce @dfn{table
261 lookup file}. @subcmd{TABLE} has same syntax as @subcmd{FILE}, and the @subcmd{RENAME}, @subcmd{IN}, and
262 @subcmd{SORT} subcommands may follow a @subcmd{TABLE} in the same way as @subcmd{FILE}.
263 Regardless of the number of @subcmd{TABLE}s, at least one @subcmd{FILE} must specified.
264 Table lookup files are treated in the same way as other input files
265 for most purposes and, in particular, table lookup files must be
266 sorted on the @subcmd{BY} variables or the @subcmd{SORT} subcommand must be specified
267 for that @subcmd{TABLE}.
269 Cases in table lookup files are not consumed after they have been used
270 once. This means that data in table lookup files can correspond to
271 any number of cases in @subcmd{FILE} input files. Table lookup files are
272 analogous to lookup tables in traditional relational database systems.
274 If a table lookup file contains more than one case with a given set of
275 @subcmd{BY} variables, only the first case is used.
278 When @cmd{MATCH FILES} creates an output case, variables that are only in
279 files that are not present for the current case are set to the
280 system-missing value for numeric variables or spaces for string
291 /FILE=@{*,'@var{file_name}'@}
292 [/RENAME=(@var{src_names}=@var{target_names})@dots{}]
297 /BY @var{var_list}[(@{D|A@})] [@var{var_list}[(@{D|A@})]]@dots{}
298 [/DROP=@var{var_list}]
299 [/KEEP=@var{var_list}]
303 @cmd{UPDATE} updates a @dfn{master file} by applying modifications
304 from one or more @dfn{transaction files}.
306 @cmd{UPDATE} shares the bulk of its syntax with other @pspp{} commands for
307 combining multiple data files. @xref{Combining Files Common Syntax},
308 above, for an explanation of this common syntax.
310 At least two @subcmd{FILE} subcommands must be specified. The first @subcmd{FILE}
311 subcommand names the master file, and the rest name transaction files.
312 Every input file must either be sorted on the variables named on the
313 @subcmd{BY} subcommand, or the @subcmd{SORT} subcommand must be used just after the @subcmd{FILE}
314 subcommand for that input file.
316 @cmd{UPDATE} uses the variables specified on the @subcmd{BY} subcommand, which is
317 required, to attempt to match each case in a transaction file with a
318 case in the master file:
322 When a match is found, then the values of the variables present in the
323 transaction file replace those variables' values in the new active
324 file. If there are matching cases in more than more transaction file,
325 @pspp{} applies the replacements from the first transaction file, then
326 from the second transaction file, and so on. Similarly, if a single
327 transaction file has cases with duplicate @subcmd{BY} values, then those are
328 applied in order to the master file.
330 When a variable in a transaction file has a missing value or when a string
331 variable's value is all blanks, that value is never used to update the
335 If a case in the master file has no matching case in any transaction
336 file, then it is copied unchanged to the output.
339 If a case in a transaction file has no matching case in the master
340 file, then it causes a new case to be added to the output, initialized
341 from the values in the transaction file.