Creating a binary variable based on previous values in a person-day dataset

[Carmine_Rossi]

Good afternoon SAS community,

I am working on an analysis of harm-reduction in a cohort of injection drug users and I have data that exists as person-day (see table below), with a binary 1/0 indicator if that patient filled a prescription for opioid-substitution that day (OST_Use). There are no gaps in person-days once follow-up starts. I am interested in creating another binary covariate (off_OST_7_cons_days) that is equal to 1 if an individual has been off OST for the past seven consecutive days, excluding the current day, and 0 otherwise, unless 7 days has not yet elapsed.

How would I go about creating this variable?  

Thank you again for considering my question,

-Carmine

Sample Dataset

ID	Date	OST_Use	off_OST_7_cons_days
1	1999Jan01	0	.
1	1999Jan02	0	.
1	1999Jan03	0	.
1	1999Jan04	0	.
1	1999Jan05	1	.
1	1999Jan06	1	.
1	1999Jan07	1	.
1	1999Jan08	1	0
1	1999Jan09	1	0
1	1999Jan10	0	0
1	1999Jan11	1	0
1	1999Jan12	0	0
1	1999Jan13	0	0
1	1999Jan14	0	0
1	1999Jan15	0	0
1	1999Jan16	0	0
1	1999Jan17	0	0
1	1999Jan18	0	0
1	1999Jan19	0	1
1	1999Jan20	1	1
1	1999Jan21	1	0
1	1999Jan22	1	0
1	1999Jan23	1	0
1	1999Jan24	1	0
1	1999Jan25	1	0
2	2004Feb02	0	.
2	2004Feb03	0	.
...	...	...	... (continues)

[AMSAS]

Hi, This can be done using the retain statement

Here's an example 

/* set the seed for ranuni function */
%let seed=2 ;

/* Set up some sample data */
data input ;
	do id=1 to 3 ;
		day=0 ;
		do date="15Apr2018"d to "15May2018"d ;
			ost_use=1 ;
			if ranuni(&seed)<0.1 then do ;
				ost_use=0 ;
				if ranuni(&seed)<0.5 then do ;
					day=date ;
				end ;
			end ;
			if day ne 0 and date-day<7 then do ;
				ost_use=0 ;
			end ;
			else
				day=0 ;
			output ;
		end ;
	end ;
run ;


data output ;
	/* Retain the daysOffCntr this will count the number of days the patient is off */
	retain daysOffCntr 0 ;
	set input ;
	by id ;
	/* If it is the first occurrance of a patient ID set the counter to 0 */
	if first.id then 
		daysOffCntr=0 ;
	/* Add 1 to daysOffCntr if patient didn't take meds this day */
	if ost_use=0 then
		daysOffCntr+1 ;
	/* If they took meds reset the counter to 0 */
	else
		daysOffCntr=0 ;
	/* Check if counter is >=7 and set the off_OST_cons_days variable to 1 */
	if daysOffCntr>=7 then
		off_OST_7_cons_days=1 ;
	/* If counter <7 then set to 0 */
	else
		off_OST_7_cons_days=0 ;
run ;

[ballardw · Accepted solution]

---

@Carmine_Rossi wrote:

Good afternoon SAS community,

I am working on an analysis of harm-reduction in a cohort of injection drug users and I have data that exists as person-day (see table below), with a binary 1/0 indicator if that patient filled a prescription for opioid-substitution that day (OST_Use). There are no gaps in person-days once follow-up starts. I am interested in creating another binary covariate (off_OST_7_cons_days) that is equal to 1 if an individual has been off OST for the past seven consecutive days, excluding the current day, and 0 otherwise, unless 7 days has not yet elapsed.

How would I go about creating this variable?  

Thank you again for considering my question,

-Carmine

Sample Dataset

ID	Date	OST_Use	off_OST_7_cons_days
1	1999Jan01	0	.
1	1999Jan02	0	.
1	1999Jan03	0	.
1	1999Jan04	0	.
1	1999Jan05	1	.
1	1999Jan06	1	.
1	1999Jan07	1	.
1	1999Jan08	1	0
1	1999Jan09	1	0
1	1999Jan10	0	0
1	1999Jan11	1	0
1	1999Jan12	0	0
1	1999Jan13	0	0
1	1999Jan14	0	0
1	1999Jan15	0	0
1	1999Jan16	0	0
1	1999Jan17	0	0
1	1999Jan18	0	0
1	1999Jan19	0	1
1	1999Jan20	1	1
1	1999Jan21	1	0
1	1999Jan22	1	0
1	1999Jan23	1	0
1	1999Jan24	1	0
1	1999Jan25	1	0
2	2004Feb02	0	.
2	2004Feb03	0	.
...	...	...	... (continues)

---

I would be tempted to try this if the data is sorted by ID and Date as implied:

data want;
  set have;
  by id;
  /* compare the previous 7 records*/
  off_OST_7_cons_days = (max (lag1(OST_Use),lag2(OST_Use),lag3(OST_Use),
                     lag4(OST_Use),lag5(OST_Use),lag6(OST_Use),lag7(OST_Use)) =0);
  /* count days for the id*/
  if first.id then count=1;
  else count+1;
  /* if the count of days is less than 8 set the result to missing*/
  if count <8 then call missing(off_OST_7_cons_days);
  drop count;
run;

The use of MAX returns 0 if all 7 previous values are 0, the comparison to 0 returns 1 when true and 0 when false.

The count of records is used to see if the value should be kept or set to missing.

[mkeintz]

data want;
  set have;
  by id;
  if lag7(id)=id then off_OST_7_cons_days=(no_use_count>=7);
  if first.id then no_use_count=0;
  if ost_use=0 then no_use_count= no_use_count+1;
  else no_use_count=0;
run;

How does this work?

1. First, the "if lag7(id)=id" test assures that off_OST_7_cons_days is only calculated when you are at least into the 8th consecutive record for an id.  It will be missing for the 1st 7 obs.
   
   
2. And notice (for the 8th and later records) it is testing the count variable (no_use_count) BEFORE updating it with the current date's OST_USE dummy.  So it's evaluating prior history, as specified in the request.
3. If the current record is the start of an id, then reset the counting variable (no_use_count) to zero.
   
   
4. Notice this program uses the statement 

      if ost_use=0 then no_use_count+1;

   instead of
   

      if ost_use=0 then no_use_count= no_use_count+1;

   
   The expression "no_use_count+1" is treated as a "sum statement", which tells sas to automatically retain the value of "no_use_count" from obs to obs.  If instead it had been "no_use_count=no_use_count+1" (an "assignment statement"), then the variable would be reset to missing for each obs, requiring an explicit retain statement. 

Edited additional note: this program assume data sorted by id/date.

[s_lassen]

Here is yet another solution:

data want;
  do _N_=1 by 1 until(last.id);
    set have;
    by id;
    if _N_>7 then
      off_OST_7_cons_days=prev_use_date<date-8;
    output;
    if =OST_use then
      prev_use_date=date;
    end;
    drop prev_use_date;
run;
        

This has the advantage of working even with the odd unexpected gap in registrations.