topic Re: Calculating date of birth backwards, using a transaction date and age. in SAS Programming

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

atiedt — Wed, 24 Oct 2018 14:26:29 GMT

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!

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

PeterClemmensen — Wed, 24 Oct 2018 14:29:35 GMT

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

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

Kurt_Bremser — Wed, 24 Oct 2018 14:36:54 GMT

Intnx() DOES work:

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

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

RW9 — Wed, 24 Oct 2018 14:44:45 GMT

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.

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

ballardw — Wed, 24 Oct 2018 20:01:02 GMT

@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.

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

atiedt — Wed, 24 Oct 2018 15:55:04 GMT

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!

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

atiedt — Wed, 24 Oct 2018 15:55:52 GMT