1 @node Data Manipulation, Data Selection, Variable Attributes, Top
2 @chapter Data transformations
3 @cindex transformations
5 The PSPP procedures examined in this chapter manipulate data and
6 prepare the active file for later analyses. They do not produce output,
10 * AGGREGATE:: Summarize multiple cases into a single case.
11 * AUTORECODE:: Automatic recoding of variables.
12 * COMPUTE:: Assigning a variable a calculated value.
13 * COUNT:: Counting variables with particular values.
14 * FLIP:: Exchange variables with cases.
15 * IF:: Conditionally assigning a calculated value.
16 * RECODE:: Mapping values from one set to another.
17 * SORT CASES:: Sort the active file.
20 @node AGGREGATE, AUTORECODE, Data Manipulation, Data Manipulation
28 /OUTFILE=@{*,'filename'@}
31 /dest_vars=agr_func(src_vars, args@dots{})@dots{}
34 @cmd{AGGREGATE} summarizes groups of cases into single cases.
35 Cases are divided into groups that have the same values for one or more
36 variables called @dfn{break variables}. Several functions are available
37 for summarizing case contents.
39 At least one break variable must be specified on BREAK, the only
40 required subcommand. The values of these variables are used to divide
41 the active file into groups to be summarized. In addition, at least
42 one @var{dest_var} must be specified.
44 By default, the active file is sorted based on the break variables
45 before aggregation takes place. If the active file is already sorted
46 or otherwise grouped in terms of the break variables, specify
47 PRESORTED to save time.
49 The OUTFILE subcommand specifies a system file by file name string or
50 file handle (@pxref{FILE HANDLE}). The aggregated cases are written to
51 this file. If OUTFILE is not specified, or if @samp{*} is specified,
52 then the aggregated cases replace the active file.
54 Specify DOCUMENT to copy the documents from the active file into the
55 aggregate file (@pxref{DOCUMENT}). Otherwise, the aggregate file will
56 not contain any documents, even if the aggregate file replaces the
59 One or more sets of aggregation variables must be specified. Each set
60 comprises a list of aggregation variables, an equals sign (@samp{=}),
61 the name of an aggregation function (see the list below), and a list
62 of source variables in parentheses. Some aggregation functions expect
63 additional arguments following the source variable names.
65 Each set must have exactly as many source variables as aggregation
66 variables. Each aggregation variable receives the results of applying
67 the specified aggregation function to the corresponding source
68 variable. Most aggregation functions may be applied to numeric and
69 short and long string variables. Others, marked below, are restricted
72 The available aggregation functions are as follows:
76 Sum. Limited to numeric values.
78 Arithmetic mean. Limited to numeric values.
80 Standard deviation of the mean. Limited to numeric values.
85 @item FGT(var_name, value)
86 @itemx PGT(var_name, value)
87 Fraction between 0 and 1, or percentage between 0 and 100, respectively,
88 of values greater than the specified constant.
89 @item FLT(var_name, value)
90 @itemx PLT(var_name, value)
91 Fraction or percentage, respectively, of values less than the specified
93 @item FIN(var_name, low, high)
94 @itemx PIN(var_name, low, high)
95 Fraction or percentage, respectively, of values within the specified
96 inclusive range of constants.
97 @item FOUT(var_name, low, high)
98 @itemx POUT(var_name, low, high)
99 Fraction or percentage, respectively, of values strictly outside the
100 specified range of constants.
102 Number of non-missing values.
104 Number of cases aggregated to form this group. Don't supply a source
105 variable for this aggregation function.
107 Number of non-missing values. Each case is considered to have a weight
108 of 1, regardless of the current weighting variable (@pxref{WEIGHT}).
110 Number of cases aggregated to form this group. Each case is considered
111 to have a weight of 1, regardless of the current weighting variable.
112 @item NMISS(var_name)
113 Number of missing values.
114 @item NUMISS(var_name)
115 Number of missing values. Each case is considered to have a weight of
116 1, regardless of the current weighting variable.
117 @item FIRST(var_name)
118 First value in this group.
120 Last value in this group.
123 Aggregation functions compare string values in terms of internal
124 character codes. On most modern computers, this is a form of ASCII.
126 The aggregation functions listed above exclude all user-missing values
127 from calculations. To include user-missing values, insert a period
128 (@samp{.}) between the function name and left parenthesis
129 (e.g.@: @samp{SUM.}).
131 Normally, only a single case (for SD and SD., two cases) need be
132 non-missing in each group for the aggregate variable to be
133 non-missing. Specifying /MISSING=COLUMNWISE inverts this behavior, so
134 that the aggregate variable becomes missing if any aggregated value is
137 @cmd{AGGREGATE} both ignores and cancels the current @cmd{SPLIT FILE}
138 settings (@pxref{SPLIT FILE}).
140 @node AUTORECODE, COMPUTE, AGGREGATE, Data Manipulation
145 AUTORECODE VARIABLES=src_vars INTO dest_vars
150 The @cmd{AUTORECODE} procedure considers the @var{n} values that a variable
151 takes on and maps them onto values 1@dots{}@var{n} on a new numeric
154 Subcommand VARIABLES is the only required subcommand and must come
155 first. Specify VARIABLES, an equals sign (@samp{=}), a list of source
156 variables, INTO, and a list of target variables. There must the same
157 number of source and target variables. The target variables must not
160 By default, increasing values of a source variable (for a string, this
161 is based on character code comparisons) are recoded to increasing values
162 of its target variable. To cause increasing values of a source variable
163 to be recoded to decreasing values of its target variable (@var{n} down
164 to 1), specify DESCENDING.
166 PRINT is currently ignored.
168 @cmd{AUTORECODE} is a procedure. It causes the data to be read.
170 @node COMPUTE, COUNT, AUTORECODE, Data Manipulation
175 COMPUTE variable = expression.
177 COMPUTE vector(index) = expression.
180 @cmd{COMPUTE} assigns the value of an expression to a target
181 variable. For each case, the expression is evaluated and its value
182 assigned to the target variable. Numeric and short and long string
183 variables may be assigned. When a string expression's width differs
184 from the target variable's width, the string result of the expression
185 is truncated or padded with spaces on the right as necessary. The
186 expression and variable types must match.
188 For numeric variables only, the target variable need not already
189 exist. Numeric variables created by @cmd{COMPUTE} are assigned an
190 @code{F8.2} output format. String variables must be declared before
191 they can be used as targets for @cmd{COMPUTE}.
193 The target variable may be specified as an element of a vector
194 (@pxref{VECTOR}). In this case, a vector index expression must be
195 specified in parentheses following the vector name. The index
196 expression must evaluate to a numeric value that, after rounding down
197 to the nearest integer, is a valid index for the named vector.
199 Using @cmd{COMPUTE} to assign to a variable specified on @cmd{LEAVE}
200 (@pxref{LEAVE}) resets the variable's left state. Therefore,
201 @code{LEAVE} should be specified following @cmd{COMPUTE}, not before.
203 @cmd{COMPUTE} is a transformation. It does not cause the active file to be
206 When @cmd{COMPUTE} is specified following @cmd{TEMPORARY}
207 (@pxref{TEMPORARY}), the @cmd{LAG} function may not be used
210 @node COUNT, FLIP, COMPUTE, Data Manipulation
215 COUNT var_name = var@dots{} (value@dots{}).
217 Each value takes one of the following forms:
223 In addition, num1 and num2 can be LO or LOWEST, or HI or HIGHEST,
227 @cmd{COUNT} creates or replaces a numeric @dfn{target} variable that
228 counts the occurrence of a @dfn{criterion} value or set of values over
229 one or more @dfn{test} variables for each case.
231 The target variable values are always nonnegative integers. They are
232 never missing. The target variable is assigned an F8.2 output format.
233 @xref{Input/Output Formats}. Any variables, including long and short
234 string variables, may be test variables.
236 User-missing values of test variables are treated just like any other
237 values. They are @strong{not} treated as system-missing values.
238 User-missing values that are criterion values or inside ranges of
239 criterion values are counted as any other values. However (for numeric
240 variables), keyword MISSING may be used to refer to all system-
241 and user-missing values.
243 @cmd{COUNT} target variables are assigned values in the order
244 specified. In the command @code{COUNT A=A B(1) /B=A B(2).}, the
245 following actions occur:
249 The number of occurrences of 1 between @code{A} and @code{B} is counted.
252 @code{A} is assigned this value.
255 The number of occurrences of 1 between @code{B} and the @strong{new}
256 value of @code{A} is counted.
259 @code{B} is assigned this value.
262 Despite this ordering, all @cmd{COUNT} criterion variables must exist
263 before the procedure is executed---they may not be created as target
264 variables earlier in the command! Break such a command into two
267 The examples below may help to clarify.
271 Assuming @code{Q0}, @code{Q2}, @dots{}, @code{Q9} are numeric variables,
272 the following commands:
276 Count the number of times the value 1 occurs through these variables
277 for each case and assigns the count to variable @code{QCOUNT}.
280 Print out the total number of times the value 1 occurs throughout
281 @emph{all} cases using @cmd{DESCRIPTIVES}. @xref{DESCRIPTIVES}, for
286 COUNT QCOUNT=Q0 TO Q9(1).
287 DESCRIPTIVES QCOUNT /STATISTICS=SUM.
291 Given these same variables, the following commands:
295 Count the number of valid values of these variables for each case and
296 assigns the count to variable @code{QVALID}.
299 Multiplies each value of @code{QVALID} by 10 to obtain a percentage of
300 valid values, using @cmd{COMPUTE}. @xref{COMPUTE}, for details.
303 Print out the percentage of valid values across all cases, using
304 @cmd{DESCRIPTIVES}. @xref{DESCRIPTIVES}, for details.
308 COUNT QVALID=Q0 TO Q9 (LO THRU HI).
309 COMPUTE QVALID=QVALID*10.
310 DESCRIPTIVES QVALID /STATISTICS=MEAN.
314 @node FLIP, IF, COUNT, Data Manipulation
319 FLIP /VARIABLES=var_list /NEWNAMES=var_name.
322 @cmd{FLIP} transposes rows and columns in the active file. It
323 causes cases to be swapped with variables, and vice versa.
325 All variables in the transposed active file are numeric. String
326 variables take on the system-missing value in the transposed file.
328 No subcommands are required. The VARIABLES subcommand specifies
329 variables that will be transformed into cases. Variables not specified
330 are discarded. By default, all variables are selected for
333 The variables specified by NEWNAMES, which must be a string variable, is
334 used to give names to the variables created by @cmd{FLIP}. If
336 specified then the default is a variable named CASE_LBL, if it exists.
337 If it does not then the variables created by FLIP are named VAR000
338 through VAR999, then VAR1000, VAR1001, and so on.
340 When a NEWNAMES variable is available, the names must be canonicalized
341 before becoming variable names. Invalid characters are replaced by
342 letter @samp{V} in the first position, or by @samp{_} in subsequent
343 positions. If the name thus generated is not unique, then numeric
344 extensions are added, starting with 1, until a unique name is found or
345 there are no remaining possibilities. If the latter occurs then the
346 FLIP operation aborts.
348 The resultant dictionary contains a CASE_LBL variable, which stores the
349 names of the variables in the dictionary before the transposition. If
350 the active file is subsequently transposed using @cmd{FLIP}, this
352 be used to recreate the original variable names.
354 FLIP honors N OF CASES. It ignores TEMPORARY, so that ``temporary''
355 transformations become permanent.
357 @node IF, RECODE, FLIP, Data Manipulation
362 IF condition variable=expression.
364 IF condition vector(index)=expression.
367 The @cmd{IF} transformation conditionally assigns the value of a target
368 expression to a target variable, based on the truth of a test
371 Specify a boolean-valued expression (@pxref{Expressions}) to be tested
372 following the IF keyword. This expression is evaluated for each case.
373 If the value is true, then the value of the expression is computed and
374 assigned to the specified variable. If the value is false or missing,
375 nothing is done. Numeric and short and long string variables may be
376 assigned. When a string expression's width differs from the target
377 variable's width, the string result of the expression is truncated or
378 padded with spaces on the right as necessary. The expression and
379 variable types must match.
381 The target variable may be specified as an element of a vector
382 (@pxref{VECTOR}). In this case, a vector index expression must be
383 specified in parentheses following the vector name. The index
384 expression must evaluate to a numeric value that, after rounding down
385 to the nearest integer, is a valid index for the named vector.
387 Using @cmd{IF} to assign to a variable specified on @cmd{LEAVE}
388 (@pxref{LEAVE}) resets the variable's left state. Therefore,
389 @code{LEAVE} should be specified following @cmd{IF}, not before.
391 When @cmd{IF} is specified following @cmd{TEMPORARY}
392 (@pxref{TEMPORARY}), the @cmd{LAG} function may not be used
395 @node RECODE, SORT CASES, IF, Data Manipulation
400 RECODE var_list (src_value@dots{}=dest_value)@dots{} [INTO var_list].
402 src_value may take the following forms:
409 Open-ended ranges may be specified using LO or LOWEST for num1
410 or HI or HIGHEST for num2.
412 dest_value may take the following forms:
419 @cmd{RECODE} translates data from one range of values to
420 another, via flexible user-specified mappings. Data may be remapped
421 in-place or copied to new variables. Numeric, short string, and long
422 string data can be recoded.
424 Specify the list of source variables, followed by one or more mapping
425 specifications each enclosed in parentheses. If the data is to be
426 copied to new variables, specify INTO, then the list of target
427 variables. String target variables must already have been declared
428 using @cmd{STRING} or another transformation, but numeric target
430 be created on the fly. There must be exactly as many target variables
431 as source variables. Each source variable is remapped into its
432 corresponding target variable.
434 When INTO is not used, the input and output variables must be of the
435 same type. Otherwise, string values can be recoded into numeric values,
436 and vice versa. When this is done and there is no mapping for a
437 particular value, either a value consisting of all spaces or the
438 system-missing value is assigned, depending on variable type.
440 Mappings are considered from left to right. The first src_value that
441 matches the value of the source variable causes the target variable to
442 receive the value indicated by the dest_value. Literal number, string,
443 and range src_value's should be self-explanatory. MISSING as a
444 src_value matches any user- or system-missing value. SYSMIS matches the
445 system missing value only. ELSE is a catch-all that matches anything.
446 It should be the last src_value specified.
448 Numeric and string dest_value's should also be self-explanatory. COPY
449 causes the input values to be copied to the output. This is only value
450 if the source and target variables are of the same type. SYSMIS
451 indicates the system-missing value.
453 If the source variables are strings and the target variables are
454 numeric, then there is one additional mapping available: (CONVERT),
455 which must be the last specified mapping. CONVERT causes a number
456 specified as a string to be converted to a numeric value. If the string
457 cannot be parsed as a number, then the system-missing value is assigned.
459 Multiple recodings can be specified on a single @cmd{RECODE} invocation.
460 Introduce additional recodings with a slash (@samp{/}) to
461 separate them from the previous recodings.
463 @node SORT CASES, , RECODE, Data Manipulation
468 SORT CASES BY var_list.
471 @cmd{SORT CASES} sorts the active file by the values of one or more
474 Specify BY and a list of variables to sort by. By default, variables
475 are sorted in ascending order. To override sort order, specify (D) or
476 (DOWN) after a list of variables to get descending order, or (A) or (UP)
477 for ascending order. These apply to the entire list of variables
480 @cmd{SORT CASES} is a procedure. It causes the data to be read.
482 @cmd{SORT CASES} attempts to sort the entire active file in main memory.
483 If main memory is exhausted, it falls back to a merge sort algorithm that
484 involves writing and reading numerous temporary files.
486 @cmd{SORT CASES} may not be specified following TEMPORARY.