topic Re: Creating a binary variable based on previous values in a person-day dataset in SAS Programming

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

Carmine_Rossi — Thu, 25 Oct 2018 17:19:09 GMT

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)

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

AMSAS — Thu, 25 Oct 2018 18:25:34 GMT

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 ;

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

ballardw — Thu, 25 Oct 2018 19:55:38 GMT

@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.

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

mkeintz — Fri, 26 Oct 2018 00:54:22 GMT

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?

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.
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.
If the current record is the start of an id, then reset the counting variable (no_use_count) to zero.
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.

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

s_lassen — Fri, 26 Oct 2018 09:09:04 GMT

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.