Stuck on SQL intck query

[ckyborg4]

I want to calculate the age of a customer but sas is giving me errors. Anyone know how to fix this?

My Code:

%let todaysDate = %sysfunc(today()) format date09.;

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_TABLE AS
SELECT
t1.SubscriberKey,
Datepart(t1.CustDOB) format date09. AS DOB,
&todaysDate AS TodaysDate,
intck('year', DOB, TodaysDate) as Age
FROM
CASE.CUSTOMERS t1
;
QUIT;
RUN;

Error Log:

ERROR: Function INTCK requires a numeric expression as argument 2.

ERROR: Function INTCK requires a numeric expression as argument 3.

ERROR: The following columns were not found in the contributing tables: DOB, TodaysDate.

[ballardw]

If you do

%put &todaysdate. ;

you can see that the value of &todaysdate is

    %put todaysdate is: &todaysdate.;
todaysdate is: 22379 format date09.

Which in not way resembles and number. And if you actually have the value formatted it is still not a number.

You want for this example:

%let todaysDate = %sysfunc(today());

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_TABLE AS
SELECT
t1.SubscriberKey,
Datepart(t1.CustDOB) format date09. AS DOB,
&todaysDate AS TodaysDate,
intck('year', DOB, TodaysDate) as Age
FROM
HACKCASE.CUSTOMERSCOTIAREWARDS t1
;
QUIT;
RUN;

[mkeintz]

I believe you need "calculated dob" and "calculated todaysdate".

[ckyborg4]

I tried copy pasting the same code in, but it still gives me the same errors. I ran the code without the intck line and checked the column value type, they both indicated they were of type numeric. 

[sbxkoenk]

Hello,

this working code should get you where you want to be.

data abc;
 set sashelp.class;
 dob=INTNX('DAY',today(),-age*365);
 format dob ddmmyy10.;
run;

%let todaysDate = %sysfunc(today());

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_TABLE AS
SELECT
t1.Name,
t1.dob format=date09. AS DOB,
&todaysDate. AS TodaysDate format=ddmmyy10.,
intck('year', DOB, &todaysDate.) as Age
FROM
work.abc t1
;
QUIT;
/* end of program */

Koen

[Patrick · Accepted solution]

intck() returns the number of interval boundaries. If the interval is year then the number of boundaries between 31Dec2020 and 01Jan2021 would be 1.

If you are really after the age then using the yrdif() function with basis 'age' is likely more what you're after.

%let todaysDate = %sysfunc(today());

PROC SQL;
  CREATE TABLE WORK.QUERY_FOR_TABLE AS
    SELECT 
      t1.SubscriberKey,
      Datepart(t1.CustDOB) format date09. AS DOB format=date9.,
      &todaysDate AS TodaysDate format=date9.,
      floor(yrdif(Datepart(t1.CustDOB), &todaysDate,'age')) as Age
    FROM
      CASE.CUSTOMERS t1
  ;
QUIT;

yrdif() returns the age with a decimal component. Use floor() - or int() - to only get the integer part of the age.