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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.