DATA Step, Macro, Functions and more

sql to find patients in all fiscal years

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

sql to find patients in all fiscal years

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;

Accepted Solutions
Solution
‎05-06-2016 12:17 PM
Contributor
Posts: 39

Re: sql to find patients in all fiscal years

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;

View solution in original post


All Replies
Solution
‎05-06-2016 12:17 PM
Contributor
Posts: 39

Re: sql to find patients in all fiscal years

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 149 views
  • 0 likes
  • 2 in conversation