sql to find patients in all fiscal years

[jenim514]

Hi!  I need some help troubleshooting a sql table I am trying to create.

I have five fiscal year tables.  I need to create a table representing only those patients that have observations in all five fiscal years. 

I ran this syntax, and checked the admitdate for first.patientid.  All first.patientid's should be in the first fical year (similarly all last.patientids should have admitdates in the last fiscal year) but it is not accurate.

proc sql;
create table CRG.new as
select a.patientid, a.sex, a.birthdate, a. sponssn, a.CONCURRENT_FY10, 
b.CONCURRENT_FY11
c.CONCURRENT_FY12, 
d.CONCURRENT_FY13,
e.CONCURRENT_FY14,
from  CRG.fy10_crg2 as a, CRG.fy11_crg2  as b, CRG.fy12_crg2 as c, CRG.fy13_crg2 as d, CRG.fy14_crg2 as e
where a.patientid=b.patientid=c.patientid=d.patientid=e.patientid;
quit;

*Unique patients identified that have encounters during each year of the study.;

proc sql;
create table CRG.FY10to14_Full as
select a.*, b.*
from CRG.new as a, CRG.FY10_14_excl_f as b
where a.patientid=b.patientid;
quit;

*join patients from 5-year cohort back to all their medical records to check first and last admit dates .  First admit date should be in FY10 and last admit date should be in FY14;

[Pamela_JSRCC · Accepted solution]

Not sure if I understand what all your variables mean, so I'm "creating" admit_date to stand in for what you said you wanted from the tables.  Also, I'm assuming that patients can be admitted more than once in a year.

proc sql;

create table CRG.new as

select a.patientid, a.sex, a.birthdate, a.sponssn,

       min(a.admit_date) as first_admit_date,

       max(e.admit_date) as last_admit_date,

from CRG.fy10_crg2 as a,

     CRG.fy11_crg2 as b,

     CRG.fy12_crg2 as c,

     CRG.fy13_crg2 as d,

     CRG.fy14_crg2 as e

where a.patientid=b.patientid=c.patientid=d.patientid=e.​patientid

group by a.patientID, a.sex, a.birthDate, a.SponSSN;

quit;

Also, another syntax for the join:

proc sql;

create table CRG.new as

select a.patientid, a.sex, a.birthdate, a.sponssn,

       min(a.admit_date) as first_admit_date,

       max(e.admit_date) as last_admit_date,

from CRG.fy10_crg2 as a

join CRG.fy11_crg2 as b on b.patientID = a.patientID

join CRG.fy12_crg2 as c on c.patientID = a.patientID

join CRG.fy13_crg2 as d on d.patientID = a.patientID

join CRG.fy14_crg2 as e on e.patientID = a.patientID

group by a.patientID, a.sex, a.birthDate, a.SponSSN;

quit;