See Crunch Automation basics for more information.
About schema commands
Schema is a complete, low-level description of the type of each variable used for validation and integrity checking—data that conforms to a schema is valid. The schema commands (RENAME, CONVERT) must always appear at the top of the script, since they can change the schema of the dataset on which they’re running.
After the first non-schema command is declared, the app returns an error if any schema command is found subsequently in the script (i.e., after the schema commands run, the schema for the dataset becomes immutable).
Crunch Automation makes the schema immutable to avoid further mistakes and alignment issues (for appending data). If applicable, you should make changes to the schema prior to running the script to address any errors that occur in data collection.
You can also use the CREATE CONVERT command to create derivations (of a different variable type and alias), which are not schema-changing by definition.
About the CONVERT command
The CONVERT command allows you to change the type of the mentioned variables to another type. This mutation changes the variables' data.
It is necessary to provide options depending on the target type to indicate how to handle the values found in the original column.
When converting multiple variables into one command, they should all be of the same type.
If the input variable is a derived variable, it returns an error since it's not possible to perform casting with options to a derived variable.
Converting to categorical requires that you indicate the desired categories with their labels and optional codes, and whether it is missing or not. Any extra value in the data that isn't specified will get an automatic category assigned with the next available code.
In the case of converting numeric to categorical types, the original numeric value will be stored in each category's numeric_value attribute.
It is only possible to convert text and numeric columns to categorical.
Converting to datetime is only allowed from text and categorical variables. It is necessary to provide a date mask indicating the format to parse the values as; additionally, an optional resolution can be indicated using the following valid format:
- ["Y", "3M", "M", "W", "D", "h", "m", "s", "ms", "us"]
Anything that doesn't comply to the format will be converted to missing data.
Converting to text is only possible from numeric and categorical variables. In the case of numeric variables, the numbers will be converted to a floating-point string (adding a .0 decimal in the case of integers).
It is possible to convert text and categorical variables to numeric. In the case of categorical variables, the numeric_value attribute will be used as the final value.
CONVERT alias, ..., alias TO CATEGORICAL WITH VALUE "string"|number TO "label" [CODE code [MISSING]], ..., VALUE "string"|number TO "label" [CODE code [MISSING]], ; CONVERT alias, ..., alias TO DATETIME FORMAT "string" [RESOLUTION "string"]; CONVERT alias, ..., alias TO NUMERIC; CONVERT alias, ..., alias TO TEXT;
CONVERT var1, var2 TO NUMERIC; CONVERT var3, var4 TO CATEGORICAL WITH VALUE "a" TO "Letter A" CODE 1 MISSING, VALUE "b" TO "Letter B" CODE 2; CONVERT var3, var4 TO CATEGORICAL WITH VALUE 1 TO "First", VALUE 2 TO "Second", VALUE 3 TO "Third";