Data I Have-
Diagnosis Data -
Pat_id Diag_cd Diag_dt
001 a 15jul2009
001 b 15jul2009
001 c 15jul2009
001 a 29aug2010
001 b 06jan2014
001 d 06jan2014
001 e 15feb2015
Inpatient Data -
Pat_id admit_dt discharg_dt
001 29aug2010 07sep2010
001 10feb2015 19feb2015
001 15jul2009 29jul2009
Data Want -
Pat_id Diag_cd Diag_dt Flag
001 a 15jul2009 Y
001 b 15jul2009 N
001 c 15jul2009 N
001 a 29aug2010 Y
001 b 06jan2014 N
001 d 06jan2014 N
001 e 15feb2015 Y
The logic of Flag column is -
If a diag_dt falls between any of the admit_dt and discharg_dt (both inclusive) then
flag should be Y else flag N.
So i need to to compare each diag_dat of diagnosis data with all the admit and discharge date of inpatient data .
How can it be done ?Any leads will be appreciated .
But the output doesn't look like what you are talking about.
data a;
input Pat_id $ Diag_cd $ Diag_dt : date9.;
format diag_dt date9.;
cards;
001 a 15jul2009
001 b 15jul2009
001 c 15jul2009
001 a 29aug2010
001 b 06jan2014
001 d 06jan2014
001 e 15feb2015
;
run;
data b;
input Pat_id $ admit_dt : date9. discharg_dt : date9.;
format admit_dt discharg_dt date9.;
cards;
001 29aug2010 07sep2010
001 10feb2015 19feb2015
001 15jul2009 29jul2009
;
run;
proc sql;
create table want as
select a.*,case when(sum(Diag_dt between admit_dt and discharg_dt) ne 0) then 'Y' else 'N' end as flag
from a,b
where a.Pat_id=b.Pat_id
group by a.Pat_id,a.Diag_cd,a.Diag_dt;
quit;
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.