topic Indicating treatment within the previous two years in SAS Data Management

Indicating treatment within the previous two years

Solvej — Thu, 30 May 2019 14:58:25 GMT

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;

Re: Indication treatment within the last two years

PeterClemmensen — Thu, 30 May 2019 14:27:12 GMT

You have two dates for each data set. Which dates are to be compared?

Re: Indication treatment within the last two years

Solvej — Thu, 30 May 2019 14:41:03 GMT

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.

Re: Indication treatment within the last two years

PeterClemmensen — Thu, 30 May 2019 15:15:54 GMT

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?

Re: Indication treatment within the last two years

Solvej — Thu, 30 May 2019 15:57:40 GMT

Patient number 1 in have1 have to be compared to all treatments in have2 for patient number 1.

Re: Indication treatment within the last two years

Solvej — Thu, 30 May 2019 16:33:46 GMT

I.e.
1 1 01-01-2010 01-02-2010

have to be compared to all these linies

1 1 01-01-2007 01-02-2007

1 2 03-02-2010 28-02-2010

1 3 03-03-2017 31-03-2017

However non of them are within the to years time span prior to 01-01-2010, hence the indicator variable should be 0

Re: Indication treatment within the last two years

JackHamilton — Thu, 30 May 2019 16:50:58 GMT

So you want <= 2 years to have passed between the earliest treatment date and the latest discharge date? Or between the latest treatment date and the earliest admit date?
Also, "year" is not a well-defined term. Depending on how you define it, two years could be anywhere between 366 days and 1,095 days (look at the different definitions of year in the documentation for the YRDIF function, and that doesn't cover all the possibilities). Because SAS dates are measured in days, it would be better to say something like "within 730 days".

24 %put DAYS=%sysevalf(%sysfunc(yrdif('01jan2000'd, '31dec2002'd)), integer);
DAYS=2
25
26 %put DAYS=%sysevalf('31dec2002'd - '01jan2000'd);
DAYS=1095

Re: Indication treatment within the last two years

Solvej — Thu, 30 May 2019 17:00:47 GMT

I want to know if there is less than two years (2*365.25 days) between any of the treatment intervals that have startet prior to each of the admissions.

Re: Indicating treatment within the previous two years

PeterClemmensen — Thu, 30 May 2019 19:28:59 GMT

@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

Re: Indicating treatment within the previous two years

Solvej — Fri, 31 May 2019 19:51:42 GMT

Thank you the solution works, however I loss all the patients that have not been treated at all thus are not included in have2.

Can these be kept in some way?

Re: Indicating treatment within the previous two years

PeterClemmensen — Fri, 31 May 2019 20:09:33 GMT

So if have1 looks like below.. Should the last record be in the desired result as well?

Re: Indicating treatment within the previous two years

PeterClemmensen — Fri, 31 May 2019 20:37:56 GMT

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;

Re: Indicating treatment within the previous two years

Solvej — Fri, 31 May 2019 22:26:47 GMT

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;