topic AGE calculation with yrdif in SAS Programming

AGE calculation with yrdif

SASAna — Wed, 27 May 2020 12:49:18 GMT

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

Re: AGE calculation with yrdif

Kurt_Bremser — Wed, 27 May 2020 13:35:49 GMT

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

Re: AGE calculation with yrdif

FreelanceReinh — Wed, 27 May 2020 13:56:33 GMT

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(...

Re: AGE calculation with yrdif

ballardw — Wed, 27 May 2020 14:15:39 GMT

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;

Re: AGE calculation with yrdif

SASAna — Wed, 27 May 2020 15:52:41 GMT

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

Thanks again,
Ana

Re: AGE calculation with yrdif

SASAna — Wed, 27 May 2020 15:54:03 GMT

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