topic Stuck on SQL intck query in SAS Programming

Stuck on SQL intck query

ckyborg4 — Sat, 10 Apr 2021 07:06:37 GMT

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.

Re: Stuck on SQL intck query

ballardw — Sat, 10 Apr 2021 05:19:18 GMT

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;

Re: Stuck on SQL intck query

mkeintz — Sat, 10 Apr 2021 05:22:28 GMT

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

Re: Stuck on SQL intck query

ckyborg4 — Sat, 10 Apr 2021 07:08:06 GMT

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.

Re: Stuck on SQL intck query

sbxkoenk — Sat, 10 Apr 2021 11:39:27 GMT

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

Re: Stuck on SQL intck query

Patrick — Sun, 11 Apr 2021 01:42:18 GMT

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.