Subscribe Bookmark RSS Feed

How can I convert date of birth into age as of a certain date in JMP?

gregg_gascon

Community Trekker

Joined:

Feb 24, 2016

How can I convert date of birth into age as of a certain date in JMP?

5 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

JMP stores dates as seconds since a reference date.  So if your values are of date type, you can subtract them to determine age.  To convert to days, divide by 3600/24/365.25.  Here's a simple example that shows how to do this using a formula in a table:

New Table( "Age Calculation",

      Add Rows( 2 ),

      New Column( "DOB", Numeric, Continuous, Format( "ddMonyyyy", 10 ),

            Input Format( "ddMonyyyy" ), Set Values( [3029529600, 3197750400] )

      ),

      New Column( "Reference Date", Numeric, Continuous, Format( "ddMonyyyy", 10 ),

            Input Format( "ddMonyyyy" ), Set Values( [3507494400, 3507494400] )

      ),

      New Column( "Age", Numeric, Continuous, Format( "Fixed Dec", 12, 2 ),

            Formula( (((:Reference Date - :DOB) / 3600) / 24) / 365.25 )

      )

);

gregg_gascon

Community Trekker

Joined:

Feb 24, 2016

Thank you. I've tried something similar to that in the original file, which was in MS Excel. However, for persons < 1 year of age, the conversion gave me negative numbers.

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

The Date Difference() function is another alternative.

11057_JMPScreenSnapz001.png

The attached data table has an example.

-Jeff
markbailey

Staff

Joined:

Jun 23, 2011

Don't forget that there are quite a few date and time functions built into JMP for formulas and scripts. I like them because they are convenient, easy to remember, and the purpose is clear. For example, if I wanted to know the difference in dates as biweekly intervals, I would divide the difference in seconds by In Weeks(2). If I wanted to know the elapsed time in days, I would divide by In Days(1).

I know that it is easy to compute the number of seconds yourself, but I prefer to use these helper functions.

Go to Help and select Scripting Index. Change the focus to Functions and select the Date Time group. You can see them all at once. You can select one of them and test it on the right or click Help to go to the full documentation.

Learn it once, use it forever!
theresagoodyear

Community Trekker

Joined:

Sep 21, 2015

I create my own formula in JMP.  For example, if I want to get the member age on the date of the application, I subtract the date of birth from the application date.  Then, divide by 86400 and 365 to get the figure in years.  See below.

newcolumn("App Date - Birth Date",numeric, formula(

((:App Date - :Birth Dt) / 86400) / 365));

Once I have the member age, I group this figure by applying the formula below:

newcolumn("Member Age Range",numeric, formula(

If(

17 < :Name( "App Date - Birth Date" ) <= 25, "18-25",

25 < :Name( "App Date - Birth Date" ) <= 35, "26-35",

35 < :Name( "App Date - Birth Date" ) <= 45, "36-45",

45 < :Name( "App Date - Birth Date" ) <= 55, "46-55",

55 < :Name( "App Date - Birth Date" ) <= 65, "56-65",

65 < :Name( "App Date - Birth Date" ) <= 75, "66-75",

"76+"

)));