Creating a variable with a single value depending on another variable in a longitudinal dataset

[bgosiker]

Hi Everyone, 

I am trying to generate a variable for exposure to a specific medicine ie the variable should be 1 if medicine=1 at ANY time during followup. So, if at any time during follow-up medicine=1 then I'd like it to fill in both going forward and back with indicator=1. I've been approaching it from generating a count with n=0 at first.id and then n+1 when medicine=1 but I can't seem to figure out how to get it to fill in the indicator variable both after and before the visit where medicine=1. Thanks for any help! (PS working in SAS 9.4) 

Data structure I have: 

id   visit   medicine  

1      1         0

1      2         0

1      3         1

2      1         0

2      2         0

2      3         0

3      1         1

3      2         0 

3      3         0

3      4         0

Data strcuture I need: 

id   visit   medicine   indicator

1      1         0                 1

1      2         0                 1 

1      3         1                 1

2      1         0                 0

2      2         0                 0

2      3         0                 0

3      1         1                 1

3      2         0                 1

3      3         0                 1 

3      4         0                 1

[novinosrin]

proc sql;

create table want as

select *, max(medicine) as indicator

from have

group by id;

quit;

[novinosrin]

data have;
input id   visit   medicine ;
cards;
1      1         0
1      2         0
1      3         1
2      1         0
2      2         0
2      3         0
3      1         1
3      2         0 
3      3         0
3      4         0
;

data want;
merge have have(drop=visit in=b rename=(medicine=m) where=(m=1))	;
by id;
indicator= b;
drop m;
run;

[PGStats]

Clever, but you need to drop or change the name of visit in the second dataset, not to overwrite it. I propose:

data want;
merge have have(keep=id medicine rename=medicine=indicator where=(indicator));
by id;
indicator = coalesce(indicator, 0);
run;

I like this kind of clever coding, but I don't use it in real life, unless there is no other way. It's just too hard to maintain.

[novinosrin]

Yes sir, noticed that later and edited. Too tired, was a long day:(  Thank you as always. You have always been my inspiration

[PGStats · Accepted solution]

Or if you prefer sequential processing (usually faster, for large datasets)

data want;
do until(last.id);
    set have; by id;
    indicator = max(indicator, medicine);
    end;
do until(last.id);
    set have; by id;
    output;
    end;
run;

[novinosrin]

Hash application here is ridiculously easy and lazy plus no sorting required

data have;
input id   visit   medicine ;
cards;
1      1         0
1      2         0
1      3         1
2      1         0
2      2         0
2      3         0
3      1         1
3      2         0 
3      3         0
3      4         0
;

data want;
if _n_=1 then do;
   dcl hash H (dataset:'have(keep=id medicine where=(medicine=1)') ;
   h.definekey  ("id") ;
   h.definedone () ;
   end;
set have;
indicator=h.check()=0;
run;

[bgosiker]

Thank you everyone for all of the options --- exactly what I wanted it to do. I really appreciate the help!