topic Re: How to code a look back period to create a history of disease indicator in SAS Programming

How to code a look back period to create a history of disease indicator

brian93 — Tue, 08 Apr 2025 19:15:08 GMT

Hello, I am trying to figure out the best way to program in SAS a way to do a "look-back" where I can apply a patient's history of different health conditions to more recent visits, in particular in the event the patient has missing diagnosis data for some visits. I would like to implement a 1-year "look-back" period to see if a patient has had a history of different diseases and indicate that in a new 0/1 or Yes/No variable.

The term lookback may be a misnomer where I believe the best way to program this in SAS would to be to sort by patient ID and visit date use a RETAIN statement in a data step and then retain this value for a year if Hypertension=1. But if there is a better way to program this in SAS then that would be great too.

I am working with medical visit data where I have unique patient ID, data of visit, and a dummy variable indicator for presence of hypertension recorded at that visit. I have provided some example data below, and included the desired outcome in the column Hypertension_Lookback for a 1-year lookback period. In this example Patient 1 would have more recent visits where Hypertension_Lookback=1, as would Patient 2 for their most recent visit, but not their earlier visit before their first recorded instance of hypertension. Additionally, if I am using a 1-year cutoff, patient 5 would not have a history of hypertension indicated as the most recent visit was more than 1 year from the last visit with an indicator of Hypertension.

PatientId	VisitDate	Hypertension	Hypertension_Lookback
1	1/1/2023	1	1
1	2/1/2023	0	1
1	3/1/2023	0	1
2	1/1/2023	0	0
2	2/1/2023	1	1
2	3/1/2023	0	1
3	1/1/2023	1	1
3	2/1/2023	1	1
4	1/1/2023	0	0
4	2/1/2023	0	0
5	1/1/2023	1	1
5	2/1/2024	0	0

Re: How to code a look back period to create a history of disease indicator

Quentin — Wed, 09 Apr 2025 01:01:02 GMT

The RETAIN approach you describe sounds reasonable to me. Have you tried it?

Please post the example data as a data step reading in the data with CARDS statement, and please post the coded you have tried. And please describe whether the code you tried is giving you errors, or just incorrect results.

Posting the data and the code you have tried will help others help you.

BTW, if you don't want the contemporaneous HYPERTENSION variable to impact HT_LOOKBACK (i.e. you only want to consider preceding observations, then just reorder two statements, so that you would have:

  ht_lookback= (visitdate<=cutoff_date) ;
  if hypertension=1 then cutoff_date = intnx('year',visitdate,1,'same') ;

Re: How to code a look back period to create a history of disease indicator

quickbluefish — Tue, 08 Apr 2025 23:07:29 GMT

What is the date from which you're looking back? Is it the same for each person? Below I'm just assuming you have some variable like 'indexdate' which is your end of lookback. I usually do this kind of thing in SQL if it's just a few conditions. The below uses the useful (and slightly disconcerting) fact that PROC SQL doesn't complain about division by zero, so dividing by the 0/1 variable hypertension preserves this part: (indexdate-365<=visitdate<indexdate) as either 0 or 1 if hypertension=1 and forces it to missing otherwise.

proc sql;
create table LB as
select patient, indexdate, 
max((indexdate-365<=visitdate<indexdate)/hypertension) as LB_hypertension
from have group by patient, indexdate;
quit;

Re: How to code a look back period to create a history of disease indicator

mkeintz — Wed, 09 Apr 2025 18:51:20 GMT

Yes, RETAIN is a good way to go, assuming the data are sorted by ID/visitdate. For each obs, just retain a cutoff date representing the 1 year window you want to examine for upcoming observations.

data have;
 input PatientId	VisitDate :mmddyy10.	Hypertension	Hypertension_Lookback;
 format visitdate mmddyy10.;
datalines;
1	1/1/2023	1	1
1	2/1/2023	0	1
1	3/1/2023	0	1
2	1/1/2023	0	0
2	2/1/2023	1	1
2	3/1/2023	0	1
3	1/1/2023	1	1
3	2/1/2023	1	1
4	1/1/2023	0	0
4	2/1/2023	0	0
5	1/1/2023	1	1
5	2/1/2024	0	0
run;
data want (drop=_:);
  set have;
  by patientid;

  retain _cutoff_date;
  if first.patientid then call missing(cutoff_date);
  if hypertension=1 then cutoff_date=intnx('year',visitdate,1,'same');

  ht_lookback= (visitdate<=cutoff_date);
run;

Re: How to code a look back period to create a history of disease indicator

Ksharp — Wed, 09 Apr 2025 01:13:08 GMT

So what is your desired output ?