CODEWITHSUNDEEP
sas
user3566853
user3566853

Reputation: 31

SAS: code to create 'ever' variable for subsequent obsevations once event occurs

data have;
input ID Herpes;
datalines;
111 1
111 .
111 1
111 1
111 1
111 .
111 .
254 0
254 0
254 1
254 .
254 1
331 1
331 1
331 1
331 0
331 1
331 1
;

Where 1=Positive, 0=Negative, .=Missing/Not Indicated

Observations are sorted by ID (random numbers, no meaning) and date of visit (not included because not needed from here forward). Once you have Herpes, you always have Herpes. How do I adjust the Herpes variable (or create a new one) so that once a Positive is indicated (Herpes=1), all following obs will show Herpes=1 for that ID?

I want the resulting set to look like this:

111 1
111 1  (missing changed to 1)
111 1
111 1
111 1  (missing changed to 1)
111 1  (missing changed to 1)
111 1
254 0
254 0
254 1
254 1  (missing changed to 1 following positive at prior visit)
254 1
331 1 
331 1
331 1
331 1  (patient-indicated negative/0 changed to 1 because of prior + visit)
331 1
331 1

Upvotes: 2

Views: 91

Answers (3)

Joe
Joe

Reputation: 63434

This can also be done in SQL. Here is an example using UPDATE to update the table in place. (This could also be done in base SAS with MODIFY.)

proc sql undopolicy=none;
update have H
    set herpes=1 where exists (
        select 1 from have V
        where h.id=v.id 
            and h.dtvar ge v.dtvar
            and v.herpes=1
    );
quit;

The SAS version using modify. BY doesn't work in a one-dataset modify for some reason, so you have to do your own version of first.id.

data have;
modify have;
drop _:;
retain _t _i;
if _i ne id then _t=.;
_i=id;
_t = _t or herpes;
if _t then herpes=1;
run;

Upvotes: 0

rambles
rambles

Reputation: 706

You could create a new variable that sums the herpes flag within ID:-

proc sort data=have;
  by id;
data have_too;
  set have;
  by id;

  if first.id then sum_herpes_in_id = 0;
  sum_herpes_in_id ++ herpes;
run;

That way it's always positive from the first time herpes=1 within id. You can access these observations in other datasteps / procs with where sum_herpes_in_id;.

And for free, you also have the total number of herpes flags per id (if that's of any use).

Upvotes: 0

Robert Penridge
Robert Penridge

Reputation: 8523

The below code should do the trick. The trick is to use by-group processing in conjunction with the retain statement.

proc sort data=have;
  by id;
run;

data want;
  set have;
  by id;

  retain uh_oh .;

  if first.id then do;
    uh_oh = .;
  end;

  if herpes then do;
    uh_oh = 1;
  end;

  if uh_oh then do;
    herpes = 1;
  end;

  drop uh_oh;
run;

Upvotes: 2

Related Questions