topic Re: How to compare dates in two tables? in SAS Programming https://communities.sas.com/t5/SAS-Programming/How-to-compare-dates-in-two-tables/m-p/346149#M79768 <P>But the output doesn't look like what you are talking about.</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">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;</CODE></PRE> Fri, 31 Mar 2017 14:04:14 GMT Ksharp 2017-03-31T14:04:14Z How to compare dates in two tables? https://communities.sas.com/t5/SAS-Programming/How-to-compare-dates-in-two-tables/m-p/345952#M79671 <P>Data I Have-</P><P>&nbsp;</P><P>Diagnosis Data -</P><P>Pat_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Diag_cd &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Diag_dt<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15jul2009<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15jul2009<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15jul2009<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29aug2010<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06jan2014<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06jan2014<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15feb2015</P><P>&nbsp;</P><P>Inpatient Data -</P><P><BR />Pat_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; admit_dt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;discharg_dt<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29aug2010 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07sep2010<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10feb2015 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19feb2015<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15jul2009 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29jul2009</P><P><BR />Data Want -</P><P>&nbsp;</P><P>Pat_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Diag_cd &nbsp; &nbsp; &nbsp; &nbsp;Diag_dt &nbsp; &nbsp; &nbsp; &nbsp; Flag<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15jul2009 &nbsp; &nbsp; &nbsp; &nbsp; Y<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15jul2009 &nbsp; &nbsp; &nbsp; &nbsp; N<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15jul2009 &nbsp; &nbsp; &nbsp; &nbsp; N<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;29aug2010 &nbsp; &nbsp; &nbsp; Y<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06jan2014 &nbsp; &nbsp; &nbsp; &nbsp;N<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06jan2014 &nbsp; &nbsp; &nbsp; &nbsp;N<BR />001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;e &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15feb2015 &nbsp; &nbsp; &nbsp; &nbsp;Y</P><P>&nbsp;</P><P>The logic of Flag column is -</P><P>If a diag_dt falls between any of the admit_dt and discharg_dt (both inclusive) then<BR />flag should be Y else flag N.<BR />So i need to to compare each diag_dat of diagnosis data with all the admit and discharge date of inpatient data .</P><P>How can it be done ?Any leads will be appreciated .</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P> Thu, 30 Mar 2017 20:11:43 GMT https://communities.sas.com/t5/SAS-Programming/How-to-compare-dates-in-two-tables/m-p/345952#M79671 ani_89 2017-03-30T20:11:43Z Re: How to compare dates in two tables? https://communities.sas.com/t5/SAS-Programming/How-to-compare-dates-in-two-tables/m-p/346149#M79768 <P>But the output doesn't look like what you are talking about.</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">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;</CODE></PRE> Fri, 31 Mar 2017 14:04:14 GMT https://communities.sas.com/t5/SAS-Programming/How-to-compare-dates-in-two-tables/m-p/346149#M79768 Ksharp 2017-03-31T14:04:14Z