Batch Transformation Formulas

Ribbon bar. When a spreadsheet is displayed, select the Data tab. In the Transformations group, click Transforms to display the Batch Transformation Formulas dialog box.

Classic menus. On the spreadsheet Data menu, select Batch Transformation Formulas to display the Batch Transformation Formulas dialog box.

These data transformation options supplement the data transformation formulas built into the spreadsheet (see How do I calculate (transform) values in a variable? for formulas that can be entered into the long variable name field of individual variables); you can enter several transformation formulas into a text editor and evaluate these transformations in sequence, one by one. See also, Spreadsheet Formulas - Overview and Spreadsheet Formulas - Syntax Summary for syntax overviews and examples.

Efficient processing for large data sets. This option is particularly well suited (optimized) for transforming large data sets. 1) All transformation formulas specified here are evaluated case by case in a single pass through the data, and 2) any variable names or references on the left sides of equations that cannot be matched with variable names in the current data file can automatically be created as the transformation formulas are evaluated. See also the Append new variable(s) for assignments to unrecognized names on the left hand side of equations option below.

Formulas. Specify here the list of transformation formulas to be applied to the active data spreadsheet. Separate each transformation formula by a return (press ENTER on your keyboard). Note that you can also transform text variables, using the appropriate arithmetic and logical operators and functions [see Transformation of Text Variables (Variables of Type Text)]. You can also add new variables of type text, as described in the paragraph "Append new variable(s)..." below.

The only differences in syntax between the Batch Transformation Formulas and the Spreadsheet Formulas is the support for multiple formulas in the Batch option, and also the fact that because the batch formulas are not attached to any specific variable (in fact they can be freely copied from data file to data file), they cannot start with an equal sign, but must have a target variable (e.g., v1=... or Measure03=...) so that Statistica knows to which variable each formula should apply. There is also an option to "distribute" all batch formulas into the respective variables in the spreadsheet and save them there with the data file, effectively replacing the spreadsheet formulas (if there are any).

The transformations can include all of the functions supported in the spreadsheet formulas (see How do I calculate (transform) values in a variable?; click the Functions button to display the Function Browser). For general syntax rules, refer to Spreadsheet Formulas - Overview and Spreadsheet Formulas - Syntax Summary. The formulas will be evaluated one by one, in sequence, so that any results of one transformation in the list may serve as the input for the next. Thus, it is possible to create a new variable with one formula and then use that variable in subsequent formulas.

How to handle Spreadsheet formulas. The options in this drop-down list determine how the batch transformation formulas specified here will be evaluated when Spreadsheet formulas are specified as well for some or all of the variables that are to be transformed.

Ignore (do not recalculate) Spreadsheet formulas. Select this command to ignore any existing Spreadsheet formulas for the variables in the current data file. When you click the OK button, STATISTICA will only apply the transformations specified on this dialog.

Recalculate Spreadsheet formulas before executing Batch formulas. Select this command to first recalculate all Spreadsheet formulas for the current data spreadsheet, followed by the transformations specified in this dialog.

Recalculate Spreadsheet formulas after executing Batch formulas. Select this command to first apply the transformation formulas specified in this dialog box, followed by any Spreadsheet formulas that may exist for any of the variables.

Place these Batch formulas in the Spreadsheet after recalculation (replace Spreadsheet formulas if they already exist). Select this check box to "distribute" all batch formulas into the respective variables in the spreadsheet and save them there with the data file, effectively replacing the spreadsheet formulas (if there are any). Specifically, Statistica executes all batch transformation formulas, and then places the last transformation formula specified for each variable (that appears on the left side of a transformation equation in the list of transformations specified here) into the long variable name field of that variable, as a new spreadsheet formula; note that this process will replace any existing spreadsheet transformations.

Append new variable(s) for assignments to unrecognized names on left hand side of equations. When this check box is selected, the program will automatically create new variables in the current data file when variable names are encountered on the left side of any transformation equations that cannot be matched with existing variable names in the current data file. For example, suppose you entered the equation MyNewVar=v0 [i.e., you want to assign the case numbers (referenced as v0 in transformations) to the new variable MyNewVar]. If the current data file does not contain a variable called MyNewVar, and if this check box is selected, that variable will automatically be created (appended to the current data file after the last variable; see also the note regarding text variables below). If this option is not selected, the transformation equation would be invalid (since the string MyNewVar couldn't be resolved). In this case, the program displays a warning message allowing you to either create new variables to accommodate unreferenced names (on the left side of equations; i.e., to select the check box at this point), or to Cancel the current transformations. Note that once this check box is selected, any misspellings of variable names that you may enter into the left sides of transformation equations will cause the program to add new variables.

Text operations and creating new variables of type text (NewVar$[20]=Text). You can also create new variables of type Text (see Variable Data Types) using the method described above. Specifically, to create a new variable of type text, and to fill it with some new text computed, for example, as the concatenation of the text in two other variables of type text, you can use the convention:

NewVar$[len]=Text

This will create a new variable named NewVar (without the dollar sign), of width (length) len. For example, you could type:

NewVar$[20]="String is "+V2

This would concatenate the constant string "String is" with the contents of text variable V2 [see also Transformation of Text Variables (Variables of Type Text)]. Note that this equation would fail if V2 contained numeric values (or numeric values with text labels; see Notes on Text Labels and Text Values), and in this case the missing data code would be assigned to the values of the new variables instead.

Recalculate all variables. This option is available only when you specify to recalculate the spreadsheet formulas before or after the (batch transformation) formulas are applied. If this check box is selected, the program will recalculate the spreadsheet formulas for all variables in the current input data file; if this check box is not selected, the spreadsheet formulas for only those variables referenced in the formulas specified here (in the Batch Transformation Formulas dialog box) are recomputed.

OK. Click OK to apply the current list of transformations.

Cancel. Click Cancel to close this dialog box without applying the current list of transformations.

Open. Click Open to load a list of transformations from a text file into the Formulas edit field.

Save As. Click this button to display a standard Save File As dialog box where you can enter a name for the text file in which to save the current list of transformation formulas. Note that the transformation formulas will be appended to other text (information) in the selected file if it already exists. For example, you could save the transformation formulas in the same file as the spreadsheet case selection conditions for the respective data spreadsheet (file); in that case the transformation formulas would be appended to the specifications for the case selection conditions, and both (selection conditions and transformation equations) will work properly when reloaded. However, when you save transformation formulas to a text file that already contains previously specified transformations, those will be replaced.

Functions. Click the Functions button to display the Function Browser, which is used to enter predefined functions into the formula.

Function guide. Select the Function guide check box for guidance when you are entering a formula into the Formulas box. Every time you type a letter to begin a new word (i.e. type a letter after the equal sign or after a space), the list of available Statistica functions that start with that letter will be displayed. You can select from this list (by double-clicking on the function name) to enter the selected function into the Formulas box.