Calculating date of birth backwards, using a transaction date and age.

[atiedt]

Is it possible to calculate a date of birth, working backwards, with an Age variable and a transaction date? 

For example, this code seems to work, but I am missing leap years. There doesn't seem to be a way to do this with intnx or intck.

DATA WANT;

     SET HAVE;

          IF AGE NE . AND TDATE NE. THEN DO;

          DOB = TDATE - (AGE*365.25);

          FORMAT DOB DATE9;

     END;

RUN;

Thanks for your advice!

[PeterClemmensen]

What do you mean by transaction data? What does it represent?

[Kurt_Bremser · Accepted solution]

Intnx() DOES work:

data want;
tdate = today();
age = 15;
dob = intnx('year',tdate,-age,'s');
format tdate dob yymmddd10.;
run;

[RW9]

Do note, that just having Age and a Date will not give you the date of birth, only the rough year, e.g: date 12May2018, age =12, DOB=2006, its not even possible to guarentee that is the right year.

[ballardw]

---

@atiedt wrote:

Is it possible to calculate a date of birth, working backwards, with an Age variable and a transaction date? 

For example, this code seems to work, but I am missing leap years. There doesn't seem to be a way to do this with intnx or intck.

DATA WANT;

     SET HAVE;

          IF AGE NE . AND TDATE NE. THEN DO;

          DOB = TDATE - (AGE*365.25);

          FORMAT DOB DATE9;

     END;

RUN;

Thanks for your advice!

---

Do you have multiple "transactions" for any of these people? Perhaps find the estimated DOB as you are doing then take a mean across the multiple transactions.

What ever approach, if you do have multiple transactions you likely should do something to derive a single DOB per person.

Note that if either of AGE or TDATE are missing then the calculation would result in missing. Which is the same result as skipping the calculation. So might as well not bother with the IF/Then/Do/End portion at all. If your data does something on the silly side such as use a specific date such as 1/1/9999 for "missing" then you would want to exclude for those dates though. Actually I would have set those to missing before getting this far.

CAUTION: Using a value such as DOB = TDATE - (AGE*365.25); with an associated decimal portion, small though it may be, can introduce odd behaviors and it may be a very good idea to round or truncate the decimal portions.

An example is comparison to fixed values:

data example;
   x= '01JAN2018'd + .01;
   if x le '01JAN2018'd then put 'Date in wanted range <= 01JAN2018 '  x= date9.;
   else put 'Date not in wanted range <= 01JAN2018 ' x= date9.;
run;

would result in

Date not in wanted range <= 01JAN2018 x=01JAN2018

and you could spend a lot of time tracing down that the formatted value of the date is ignoring the decimal portion of the date value in the message or other result table while  the actual value is compared using the decimal portion.

[atiedt]

This particular data set has already been parsed for duplicate observations and there is only one transaction date for each individual. However, I appreciate the additional advice to help generalize this code to any context!

[atiedt]

This particular data set has already been parsed for duplicate observations and there is only one transaction date for each individual. However, I appreciate the additional advice to help generalize this code to any context!