topic Re: Creating a variable with a single value depending on another variable in a longitudinal dataset in SAS Data Management

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

bgosiker — Wed, 13 Feb 2019 03:00:06 GMT

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

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

novinosrin — Wed, 13 Feb 2019 03:39:21 GMT

proc sql;

create table want as

select *, max(medicine) as indicator

from have

group by id;

quit;

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

novinosrin — Wed, 13 Feb 2019 05:17:14 GMT

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;

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

PGStats — Wed, 13 Feb 2019 05:04:01 GMT

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;

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

PGStats — Wed, 13 Feb 2019 05:25:32 GMT

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.

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

novinosrin — Wed, 13 Feb 2019 05:28:04 GMT

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

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

novinosrin — Wed, 13 Feb 2019 19:37:16 GMT

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;

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

bgosiker — Sat, 16 Feb 2019 18:08:41 GMT

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