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
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;
proc sql;
create table want as
select *, max(medicine) as indicator
from have
group by id;
quit;
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;
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.
Yes sir, noticed that later and edited. Too tired, was a long day:( Thank you as always. You have always been my inspiration
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.