When importing a data set with a date column that defaults to character, it is common to change it to numeric and continuous. Unfortunately if the input date format is incorrect any values that cannot be parsed are set to missing, thus you don't get a second chance at setting the input format. How do you typically handle this in an exploratory/graphical user interface analysis?
I sometimes
- duplicate the column before converting to numeric
- Create a new formula column to parse the date
To demonstrate the issue, run this code to create a table with some dates in m/d/y format, and then set that column to use d/m/y format, as shown in the screenshot below.
names default to here(1);
//Make a table with dates in m/d/y format
vals = index(today()-in days(100), today(), incr=in hours(1));
dt = New Table( "Example Data for Input Date Format",
Add Rows( n items(vals) ),
New Column( "Dates mdy",
Numeric,
"Nominal",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m" ),
Set Values( vals )
)
);
dt << run formulas;
wait(1);
dt:Dates mdy << data type("Character");
Now set all values at once in column info and note how some data is set to missing, even after changing the column back to character.