CONVERT (schema command)
See Crunch Automation basics for more information.
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";