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
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(...
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
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(...
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.