0

I want to calculate 'age last birthday' on a specific evaluation date, given a specific date of birth, using a SAS PROC SQL command.

How can I do this and are there any limitations?

Sample Input

DATA INPUTS;
infile cards dlm=',' dsd;
INPUT  DOBDt :DATE9. EvalDt :DATE9. expected;
FORMAT DOBDt date9. EvalDt date9.;
CARDS;
11MAY2009,10MAY2015,5
11MAY2009,11MAY2015,6
11MAY2009,12MAY2015,6
28FEB1984,01DEC2015,31
29FEB1984,28FEB2012,27
29FEB1984,29FEB2012,28
29FEB1984,01MAR2012,28
;
RUN;

The goal would be to take the dobDt as an input, evaluate on the EvalDt and produce the answer of expected

JustinJDavies
  • 2,663
  • 4
  • 30
  • 52
  • There's a good post here on how to calculate age in SAS http://blogs.sas.com/content/sasdummy/2011/07/12/computing-age-in-sas-9-3/ – Reeza Dec 21 '15 at 17:36
  • Your question is unclear to me, what does 'age last birthday on a specific evaluation date' mean. Ideally, you should also post what you've tried. Glad you found your own answer though. – Reeza Dec 21 '15 at 17:37
  • Reeza - over the past couple of years I've asked this question a couple of times and I never find the answer on stackoverflow. I thought it might help others to have my solution posted here as there are a lot of 'bad'/approximate answers on other sites. – JustinJDavies Dec 21 '15 at 21:02
  • that's fine, but the phrasing of your question is awkward and unclear. Is it the calculation of age at a specified date? Or is there some other complication that I'm not getting? – Reeza Dec 21 '15 at 21:05
  • I added a link to the definition of 'age last birthday' to clarify. (I believe the problem with this calculation in SAS and lack of a canonical example is that prior to version 9.2 there was no inbuilt functionality to calculate this result; as a result the internet is littered with broken/approximate code examples that don't actually calculate this value correctly). – JustinJDavies Dec 21 '15 at 21:06
  • 1
    So 'age last birthday' is ... age? I think you should simplify the title to just 'Calculate age in SAS' and then update the post to reflect that. You may also want to replace the 'sql' tag with 'proc-sql' so that people reading the vanilla 'sql' tag don't get confused. – Robert Penridge Dec 21 '15 at 21:14

2 Answers2

1

This can be done as such :

PROC SQL

PROC SQL;
CREATE TABLE outputs2 AS
    select
        *
        ,intck('year',DOBDt,EvalDt,'c')      AS actual
        ,((calculated actual) eq expected)   AS check
    FROM
        inputs
;
QUIT;

actual, the calculated value, matches expected, the desired outcome, for all the examples provided. I am not aware of any limitations to this approach although there are probably some extreme ages that it cannot calculate due to SAS dates having a limited range of values.

As a bonus:

DATA STEP

DATA outputs;
set inputs;
    actual = intck('year',DOBDt,EvalDt,'c');
    check  = (actual eq expected);
RUN;
JustinJDavies
  • 2,663
  • 4
  • 30
  • 52
0

This is how we used to do it back in the day. Also "age at last birthday" seems pretty clear to me.

DATA INPUTS;
   infile cards dlm=',' dsd;
   INPUT  DOBDt :DATE9. EvalDt :DATE9. expected;
   FORMAT DOBDt date9. EvalDt date9.;
   age = year(evaldt)-year(dobdt) - (month(evaldt) eq month(dobdt) and day(evaldt) lt day(dobdt)) - (month(evaldt) lt month(dobdt));
   CARDS;
11MAY2009,10MAY2015,5
11MAY2009,11MAY2015,6
11MAY2009,12MAY2015,6
28FEB1984,01DEC2015,31
29FEB1984,28FEB2012,27
29FEB1984,29FEB2012,28
29FEB1984,01MAR2012,28
;;;;
   RUN;
proc print;
   run;

enter image description here

data _null_
  • 8,534
  • 12
  • 14
  • Hmm, that doesn't look right. Fails when month(EvalDt) < month (DOBDt). Maybe one more `- (month(evaldt)< month(dobdt))` at the end? – Quentin Dec 22 '15 at 20:03