topic Re: Calculate age based on Teradata Birth Date in New SAS User

Calculate age based on Teradata Birth Date

Hdababs — Thu, 23 Mar 2023 01:23:39 GMT

Greetings,

How do I calculate age while using a connection to teradata date of birth field.

This is what I tried so far, It is embded

PROC SQL;

CONNECT TO teradata (AUTHDOMAIN=TeradataAuth TDPID=XYZ2 MODE=TERADATA CONNECTION=GLOBAL);

...

select
DMA.MCID
,frfm.HCC_CD
,DMA.GNDR_NM
,floor ((intck('month',DMA.MBR_BRTH_DT,today()) - (day(today()) < day(DMA.MBR_BRTH_DT))) / 12) as age
,INCRD_YEAR_MNTH_NBR
,case when max(PRORTD_MBR_MNTH_CNT) >0 then 1 else 0 END AS PRORTD_MBR_MNTH_CNT

ERROR: Teradata prepare: Syntax error: expected something between '(' and the string 'month'.

Re: Calculate age based on Teradata Birth Date

SASKiwi — Thu, 23 Mar 2023 01:39:32 GMT

You are trying to run SAS functions like INTCK and TODAY on a Teradata database. It won't recognise these. Either use Teradata logic or move your SAS functions to SAS SELECT:

PROC SQL;
CONNECT TO teradata (AUTHDOMAIN=TeradataAuth TDPID=XYZ2 MODE=TERADATA CONNECTION=GLOBAL);
create table want as
select  *
,floor ((intck('month',MBR_BRTH_DT,today()) - (day(today()) < day(MBR_BRTH_DT))) / 12) as age
from connection to Teradata
(
select
DMA.MCID
,frfm.HCC_CD
,DMA.GNDR_NM
,DMA.MBR_BRTH_DT
from ......
);
quit;

Re: Calculate age based on Teradata Birth Date

Kurt_Bremser — Thu, 23 Mar 2023 06:59:09 GMT

For age calculations, use the YRDIF function in SAS code.

Re: Calculate age based on Teradata Birth Date

Hdababs — Thu, 23 Mar 2023 13:10:59 GMT

Thank you. I initially posted this as a reply to a resolved post. I was able to use teradata syntax:

('2022' - extract(year from teradata birth date column name)) as Age