Dear experts
I have two datasets - one with admission and a second one with treatments. I would like to know whether a specific patient have been treated with in the last two years. A patient can have multiple treatments and admissions.
The datasets look like have1 and have 2 (see below). Want is what I would like to see with an indicator variable showing whether this specific patient at this specific admission have been treated within the last two years.
I hope it makes sense.
Thanks a lot.
Kind regards Solvej
data have1;
input
Record_id
admission_number
(Admission_date Discharge_date ) (:ddmmyy10.)
;
format Admission_date Discharge_date ddmmyyd10.;
datalines;
1 1 01-01-2010 01-02-2010
1 2 03-03-2010 31-03-2010
1 3 03-03-2011 31-03-2011
2 1 01-01-2010 01-02-2010
2 2 01-01-2011 01-02-2011
2 3 03-03-2013 31-03-2013
2 4 03-03-2018 31-03-2018
3 2 03-03-2010 31-03-2010
3 2 03-03-2015 31-03-2015
4 2 04-02-2011 04-03-2011
;
run;
data have2;
input
Record_id
treat_number
(treatin_date treatout_date) (:ddmmyy10.)
;
format treatin_date treatout_date ddmmyyd10.;
datalines;
1 1 01-01-2007 01-02-2007
1 2 03-02-2010 28-02-2010
1 3 03-03-2017 31-03-2017
2 1 01-01-2006 01-02-2006
2 2 01-01-2008 01-03-2008
2 3 03-03-2017 31-03-2017
3 1 03-03-2017 31-03-2017
4 1 03-03-2010 31-03-2010
4 2 04-02-2012 04-03-2012
;
run;
data want;
input
Record_id
admission_number
(Admission_date Discharge_date ) (:ddmmyy10.)
treated_2years_prior;
format Admission_date Discharge_date ddmmyyd10.;
datalines;
1 1 01-01-2010 01-02-2010 0
1 2 03-03-2010 31-03-2010 1
1 3 03-03-2011 31-03-2011 1
2 1 01-01-2010 01-02-2010 1
2 2 01-01-2011 01-02-2011 0
2 3 03-03-2013 31-03-2013 0
2 4 03-03-2018 31-03-2018 1
3 2 03-03-2010 31-03-2010 0
3 2 03-03-2015 31-03-2015 0
4 2 04-02-2011 04-03-2011 1
;
run;
You have two dates for each data set. Which dates are to be compared?
Have 1 includes the admission and have 2 the treatments. Have 2 treat_out date have to be maximum two years prior to admission in order for the indicator variable to be 1.
Ok. And about your admission_number and treat_number.. Are these to be compared? I mean, are record_id=1, admission_number=1 in have1 to be compared to record_id=1, treat_number=1 in have2?
Put another way: What date should 01-01-2010 in observation 1 in have1 be compared to in have2?
Patient number 1 in have1 have to be compared to all treatments in have2 for patient number 1.
@Solvej , I believe this gives you what you want?
proc sql;
create table temp as
select have1.*,
have2.treatout_date
from have1, have2
where have1.Record_id=have2.Record_id;
quit;
data want(drop=treatout_date);
treated_2years_prior=0;
do until (last.admission_number | last.Admission_date);
set temp;
by admission_number notsorted Admission_date notsorted;
if intnx('year', Admission_date, -2, 's') le treatout_date le Admission_date then treated_2years_prior=1;
end;
run;
which gives
So if have1 looks like below.. Should the last record be in the desired result as well?
If I am correct, then this is a quick fix for the code I provided already
proc sql;
create table temp2 as
select *, 0 as treated_2years_prior from have1 where Record_id not in
(select distinct Record_id from have2);
quit;
proc append base=want data=temp2;
run;
How come the result change if I sort the admission data prior to the procedures? How can I get the right result everytime not depending on how the data is sorted?
I have added a sorting statement to the below procedure. And notice that I have removed the admission number because I dont have that after all.
data have1;
input
Record_id
(Admission_date Discharge_date ) (:ddmmyy10.)
;
format Admission_date Discharge_date ddmmyyd10.;
datalines;
1 01-01-2010 01-02-2010
1 03-03-2010 31-03-2010
1 03-03-2011 31-03-2011
2 01-01-2010 01-02-2010
2 01-01-2011 01-02-2011
2 03-03-2013 31-03-2013
2 03-03-2018 31-03-2018
3 03-03-2010 31-03-2010
3 03-03-2015 31-03-2015
4 04-02-2011 04-03-2011
;
run;
data have2;
input
Record_id
(treatin_date treatout_date) (:ddmmyy10.)
;
format treatin_date treatout_date ddmmyyd10.;
datalines;
1 01-01-2007 01-02-2007
1 03-02-2010 28-02-2010
1 03-03-2017 31-03-2017
2 01-01-2006 01-02-2006
2 01-01-2008 01-03-2008
2 03-03-2017 31-03-2017
3 03-03-2017 31-03-2017
4 03-03-2010 31-03-2010
4 04-02-2012 04-03-2012
5 07-02-2012 02-04-2012
;
run;
/*proc sort data=have1; by record_id admission_date; run;*/
proc sql;
create table temp as
select have1.*,
have2.treatout_date
from have1, have2
where have1.Record_id=have2.Record_id;
quit;
data want(drop=treatout_date);
treated_2years_prior=0;
do until (last.record_id| last.Admission_date);
set temp;
by record_id notsorted Admission_date notsorted;
if intnx('year', Admission_date, -2, 's') le treatout_date le Admission_date then treated_2years_prior=1;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.