AGE calculation with yrdif

[SASAna]

Hi SAS users,

Need ahelp with figuring out this error in age calculations.

First service date and date of birth are coming as  DATETIME20 format like below examples.

FSTSRVDT    = 07OCT2019:00:00:00

bth_dt  =  21APR1950:00:00:00

below is my query -

proc sql;
create table age_threshold_claims as select
claim,
FSTSRVDT,
bth_dt,
case when
yrdif(datepart(bth_dt),datepart(FSTSRVDT),'AGE') <=18 then 'Y' else '' end as tag_it
from SAS_dataset
;
Quit;

Error : 

NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a missing value.
NOTE: Invalid argument to function YRDIF. Missing values may be generated.

Thanks,

Ana

[Kurt_Bremser]

With data as you described, it works:

data sas_dataset;
input claim (FSTSRVDT bth_dt) (:datetime18.);
format FSTSRVDT bth_dt datetime19.;
datalines;
1 07OCT2019:00:00:00 21APR1950:00:00:00
;

proc sql;
create table age_threshold_claims as select
claim,
FSTSRVDT,
bth_dt,
case when
yrdif(datepart(bth_dt),datepart(FSTSRVDT),'AGE') <=18 then 'Y' else '' end as tag_it
from SAS_dataset
;
Quit;

Log:

 73         data sas_dataset;
 74         input claim (FSTSRVDT bth_dt) (:datetime18.);
 75         format FSTSRVDT bth_dt datetime19.;
 76         datalines;
 
 NOTE: The data set WORK.SAS_DATASET has 1 observations and 3 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 78         ;
 79         
 80         proc sql;
 81         create table age_threshold_claims as select
 82         claim,
 83         FSTSRVDT,
 84         bth_dt,
 85         case when
 86         yrdif(datepart(bth_dt),datepart(FSTSRVDT),'AGE') <=18 then 'Y' else '' end as tag_it
 87         from SAS_dataset
 88         ;
 NOTE: Table WORK.AGE_THRESHOLD_CLAIMS created, with 1 rows and 4 columns.
 
 89         Quit;
 NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds

[SASAna]

Thank you for testing. It was the missing values in the data.

[FreelanceReinh · Accepted solution]

Hi @SASAna,

Missing values of bth_dt or FSTSRVDT would cause these notes in the log. In this case you can avoid the notes by handling missing values explicitly, e.g., check for missings in a first WHEN condition and assign a missing value to tag_it, if any:

case when nmiss(bth_dt, FSTSRVDT) then ' '
     when yrdif(...

[SASAna]

Thank you. I found some missing Date of birth values whoch was triggering that Note/Warning.

Thanks again,
Ana

[ballardw]

You may want to see if you have missing values for either of the two variables. Might as well checi if the bth_dt is later than the fstsrvdt as well since negative ages tend to indicate other data issues.

data want;
   set have;
   where missing(fstsrvdt) or missing(bth_dt) or (bth_dt > fstsrvdt);
run;