I need to compare multiple dates + inclusion/exclusion and hoping i can have a shorter code without hardcoding Must have one m1_dt and at least two dates "dt#" within 30 days after the start_dt Must compare all date variables (dt1 to dt4) and identify if the dates were within 30 days Exclusion Code = 'DDD','EEE','FFF Identifiers yes with exlusion code + 1m and at least 2 dt within 30 days yes with 1m and at least 2 dt within 30 days no with exclusion code no without exclusion code Please run the code below to see the expected result data dataset1;
input id $1-3 start_dt 4-12 m1_dt 13-21 dt1 22-30 dt1_code $31-34 dt2 35-43 dt2_code $44-47 dt3 48-56 dt3_code $57-60 dt4 61-69 dt4_code $70-73;
datalines;
911 20200401 20200410 20200408 AAA 20200426 AAA 20200504 EEE 20200603 EEE
912 20200401 20200411 20200409 AAA 20200426 AAA 20200506 AAA 20200508 BBB
913 20200401 20200409 20200408 BBB 20200426 CCC 20200504 EEE 20200506 EEE
914 20200401 20200423 20200421 BBB 20200422 CCC
915 20200401 20200410 20200408 DDD 20200426 DDD
916 20200401 20200410 20200408 DDD
917 20200401 20200410 20200408 AAA
918 20200401 20200410 20200408 CCC
;
run;
proc sql;
create table dataset2 as select distinct
id,
input(put(start_dt,8.),yymmdd8.) as start_dt format mmddyy10.,
input(put(m1_dt,8.),yymmdd8.) as m1_dt format mmddyy10.,
input(put(dt1,8.),yymmdd8.) as dt1 format mmddyy10.,
dt1_code,
input(put(dt2,8.),yymmdd8.) as dt2 format mmddyy10.,
dt2_code,
input(put(dt3,8.),yymmdd8.) as dt3 format mmddyy10.,
dt3_code,
input(put(dt4,8.),yymmdd8.) as dt4 format mmddyy10.,
dt4_code
from dataset1; quit;
%let exlude=('DDD','EEE','FFF');
/*expected result*/
proc sql;
create table expected_result as select distinct
a.*
,case when ((m1_dt is not null and (m1_dt-start_dt)<=30)
and (((dt1 is not null and (dt1-start_dt)<=30) and (dt2 is not null and (dt2-start_dt)<=30) and
(dt3 is not null and (dt3-start_dt)<=30) and (dt4 is not null and (dt4-start_dt)<=30)) or
((dt1 is not null and (dt1-start_dt)<=30) and (dt2 is not null and (dt2-start_dt)<=30) and
(dt3 is not null and (dt3-start_dt)<=30)) or
((dt1 is not null and (dt1-start_dt)<=30) and (dt2 is not null and (dt2-start_dt)<=30)))
and (dt1_code not in &exlude. and dt2_code not in &exlude. and dt3_code not in &exlude. and dt4_code not in &exlude.))
then 'yes with 1m and at least 2 dt within 30 days'
when ((m1_dt is not null and (m1_dt-start_dt)<=30)
and (((dt1 is not null and (dt1-start_dt)<=30) and (dt2 is not null and (dt2-start_dt)<=30) and
(dt3 is not null and (dt3-start_dt)<=30) and (dt4 is not null and (dt4-start_dt)<=30)) or
((dt1 is not null and (dt1-start_dt)<=30) and (dt2 is not null and (dt2-start_dt)<=30) and
(dt3 is not null and (dt3-start_dt)<=30)) or
((dt1 is not null and (dt1-start_dt)<=30) and (dt2 is not null and (dt2-start_dt)<=30)))
and (dt1_code in &exlude. or dt2_code in &exlude. or dt3_code in &exlude. or dt4_code in &exlude.))
then 'yes with exclusion code + 1m and at least 2 dt within 30 days'
when (dt1_code in &exlude. or dt2_code in &exlude. or dt3_code in &exlude. or dt4_code in &exlude.)
then 'no with exclusion code'
when (dt1_code not in &exlude. and dt2_code not in &exlude. and dt3_code not in &exlude. and dt4_code not in &exlude.)
then 'no without exclusion code'
else 'no' end as identifier
from dataset2 a;quit;
... View more
