topic Formula to extract date from character column in Discussions

Formula to extract date from character column

poulravn — Thu, 25 Oct 2012 07:27:24 GMT

Hi, I use SAS EG and JMP. In the past I used Base SAS for PC. I am getting frustated at how seemingly simple tasks do not have simple solutions in JMP. Now for example see the attached jmp table which contains a column with character data. I need to create a column with the dates as a numeric variable. In SAS I would write:

date=input(substr("Column 1",1,10),ddmmyy10.);

but what should the formula be for a new column for this.

Regret to have to ask such simple questions, but . . .

Thanks in advance

Poul Ravn Sørensen

Re: Formula to extract date from character column

pmroz — Thu, 25 Oct 2012 12:37:11 GMT

Use the informat function in a formula column. The trick is changing the date format to one that JMP recognizes, by changing the "." characters to "/".

dt = New Table( "date as string data",

Add Rows( 10 ),

New Column( "Column 1",

Character,

Nominal,

Set Values(

{"19.10.2012 14:26:49", "15.10.2012 9:28:57", "15.10.2012 9:22:13",

"09.10.2012 11:15:18", "09.10.2012 11:07:14", "28.09.2012 15:30:41",

"28.09.2012 13:00:18", "27.09.2012 15:43:07", "27.09.2012 15:42:16",

"27.09.2012 15:41:12"}

)

);

dt << New Column( "Column 2",

Numeric,

Continuous,

Format( "d/m/y h:m:s", 22, 0 ),

Input Format( "d/m/y h:m:s", 0 ),

Formula(

Informat( Substitute( :Column 1, ".", "/" ), "d/m/y h:m:s" );

);

Re: Formula to extract date from character column

ms — Thu, 25 Oct 2012 14:56:27 GMT

The date or date/time formats that JMP recognizes appears to differ among localizations (of JMP or OS). For example, formats with "/" and ":" as in PMros' example, does not work on my computer (English JMP10, Swedish Mac OS X).

The below examples illustrate some of the caveats. The first is not recognised, while the second with hyphens and dots works as expected (but here requiring a "double substitute").

The third and fourth examples illustrate potential date ambiguity: without a format argument JMP assumes m/d/y, which may or may not be incorrect depending on the data source.

What's the best scripting practice to deal with this date format mess?

t = "12.10.2012 10:22:31";

date1 = Informat( Substitute( t, ".", "/" ), "d/m/y h:m:s" );

date2 = Informat( Substitute( Substitute( t, ".", "-" ), ":","."), "d-m-y h.m.s" );

date3 = Informat( Substr( Substitute( t, ".", "-" ), 1, 10 ), );

date4 = Informat( Substr( Substitute( t, ".", "-" ), 1, 10 ), "d-m-y" );

Show(date1, date2, date3, date4 );

/*:

date1 = .;

date2 = 12Oct2012:10:22:31;

date3 = 10Dec2012;

date4 = 12Oct2012;

Re: Formula to extract date from character column

Jeff_Perkinson — Tue, 18 Oct 2016 20:03:31 GMT

As it turns out, the second argument to Informat() is optional. Without it, JMP will examine the first argument and try to figure out the format on its own.

With this example, JMP recognized the dates with the dots as date delimiters.

t = "12.10.2012 10:22:31";

date1 = Informat( t );

Show(date1);

/*:

date1 = 10Dec2012:10:22:31;

Back to the original question, I would change the data type of the column from Character to Numeric using the Format and Input Format to automatically convert the values to JMP Datetime values.

Then, if you want just the date part of the datetime value you can subtract off the time of day:

:Column 1 - Time Of Day( :Column 1 )

-Jeff

Re: Formula to extract date from character column

poulravn — Tue, 30 Oct 2012 15:33:50 GMT

The example of PMroz did not work on my PC either (JMP 9.0.3, Win XP). However the date4 formula by MS works. The suggestion by Jeff Perkinson to do it in the table itself kind of works against the idea to do it in scripting. I prefer to have a 'toolbox' of formulas that does these kind of things, spthat I dod not have to remember how to do it each time I have a table.

Thanks all

Poul