BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Pamela_JSRCC
Quartz | Level 8

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

1 REPLY 1
Pamela_JSRCC
Quartz | Level 8

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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