UPDATE: How do I calculate a variable based on another variable value arranged in multiple rows

[ahhh]

Correction on Step3: 

Step3: if the person had a missing/unknown after first positive, we censor them there and diff=.  but changed_result=0;

Example ID9

[ahhh]

So all I want is t2 value to be printed for id7 , Right now it is missing value.

Thanks so much in advance Shmuel!

[Shmuel]

Sory, it was a typo in my last post.  See line changed:

if result = 'negative' then do;
if type =2 and diff=. /* 1st negative following positive(s) */
then do; diff = t0 - pos1d; t2=t0; flag=.; changed_result=1; end;
/* if type is 1 or 0 no change need */
end;
else do;
if type =2 /* missing following positive(s) */
then do; ***pos1d=.; *t2=.; changed_result=0; flag=.; type=0; end;   /* <<<< line changed */
end;

[ahhh]

Thank you SHMUEL 🙂

[ahhh]

Code is not working in this scenario (Please ADVISE)

10 v1 12SEP2005 positive
10 v2 27SEP2005 positive
10 v3 16MAR2006 negative
10 v4 13SEP2006 negative
10 v5 15FEB2007 negative
10 v6 06SEP2007 positive
;
run;

WHAT I WANT IN OUTPUT :

ID DIFF CHANGED_RESULT t1                     t2                               flag

10 185   1                               12SEP2005    16MAR2006                 2

date of First positive minus date of first negative (we don't care about what the results are after this)

The code I am using:

data alg2;
format id diff changed_result t1 t2 flag;
keep id diff changed_result t1 t2 flag;
set test;
by id;
retain type ; /* 1=start negative, 2=start positive, 0=changed */
retain pos1d; /* 1st positive date */
retain flag; /* 1=all negative, 2=all positive, .=changed */
retain t2; /* date used to calc diff */
retain diff changed_result 0;
result = lowcase(result);

if first.id then do;
diff=.;
if result = 'positive' then do; type=2; pos1d = t0; flag=2; end; else
if result = 'negative' then do; type=1; flag=1; end;
else do; type = 0; flag=.; end;

end; else

if result = 'positive' then do;
if type ne 2 /* 1st positive which is not 1st in ID */
and diff=. /* <<<<<<<<<<<< line added */
then do; type=2; pos1d = t0; flag=.; end;
else if flag=2 then do;
diff=t0-pos1d; t2=t0;
end;
/* if type=2 no change need */
end; else
if result = 'negative' then do;
if type =2 and diff=. /* 1st negative following positive(s) */
then do; diff = t0 - pos1d; t2=t0; flag=.; changed_result=1; end;
/* if type is 1 or 0 no change need */
end;
else do;
if type =2 /* missing following positive(s) */
then do; ***pos1d=.; *t2=.; changed_result=0; flag=.; type=0; end; /* <<<<< line changed */
end;

This is the code I am running:

[Shmuel]

I have fixed the code according to the new scenario (ID=10):

- I have added a flag SKIP - in order to skip rows comming after geting non positive folowing positive;

- made some other changes to fix t2 ant in IF LAST.ID moved the output statement to the end after all assignments.

- cancelled some unnecessary remarks;

Here is the new code (also attached as a file with hope to keep indents)

data alg2;
     format id diff changed_result t1 t2 flag;
     keep id diff changed_result t1 t2 flag;
set test;
    by id;
retain type ; /* 1=start negative, 2=start positive, 0=changed */
retain pos1d; /* 1st positive date */
retain flag; /* 1=all negative, 2=all positive, .=changed */
retain t2; /* date used to calc diff */
retain diff changed_result 0;
retain skip; /* 1=skip next rows <<<<< new line added */
result = lowcase(result);

if first.id then do;
diff=.; skip=0;
if result = 'positive' then do; type=2; pos1d = t0; flag=2; end; else
if result = 'negative' then do; type=1; flag=1; end;
else do; type = 0; flag=.; end;
end; else
if skip = 0 then do; /* <<<<< new line added */
if result = 'positive' then do;
if type ne 2 /* 1st positive which is not 1st in ID */
and diff=.
then do; type=2; pos1d = t0; flag=.; end;
else if flag=2 then do;
diff=t0-pos1d; t2=t0;
end;
/* if type=2 no change need */
end; else
if result = 'negative' then do;
if type =2 /* and diff=. /* 1st negative following positive(s) */
then do; diff = t0 - pos1d; t2=t0; flag=.; changed_result=1; skip=1; end;
/* if type is 1 or 0 no change need */
end;
else do;
if type =2 /* missing following positive(s) */
then do; changed_result=0; flag=.; type=0; skip=1; end;
end;
end; /* <<<<< new line added */

*if id=10 then output; /* unremark for test only */
if last.id then do;
if flag=1 then do; diff=.; t1=.; t2=.; end; else
if flag=2 then do; diff=t0 - pos1d; t1=pos1d; t2=t0; end;
else do;
if diff > 0
then do; t1=pos1d; /* changed_result diff and t2 retained */ end;
else do; diff=.; t1=.; t2=.; end;
end;
output;
end;
format t1 t2 date9.;
run;

[Shmuel]

by the way, flag shouldn't be 2 as its not all positive

[Shmuel]

Please find attached an alternative code, new version, with same desired results.

I hope this version is more readable, easier to understand and to maintain.

[ahhh]

You're awesome! Appreciate this help. I am getting back to SAS after a while, so this is so informative

[ahhh]

Thank you so much

[ahhh]

PLEASE HELP: 

The code is not working in this one situation:

7 v1 01jan2007 positive
7 v2 30jun2007 positive
7 v3 08nov2007
7 v4 01feb2008 positive

11 v1 09oct2006 

11 v2 10nov2006 

11 v3 12jan2007 positive

11 v4 13mar2007 postive

output i want:

id diff changed_result           t0                          t1                  flag

7  180   0                            01jan2007           30jun2007          .

11  60   0                            12jan2007           13mar2007         .

We are trying to calculate the days that they stay positive.

The code I am using:

data alg2;
format id diff reversion t1 t2 flag;
keep id diff reversion t1 t2 flag;

set test;
by id;
retain type ; /* 1=start negative, 2=start positive, 0=changed */
retain pos1d; /* 1st positive date */
retain flag; /* 1=all negative, 2=all positive, .=changed */
retain t2; /* date used to calc diff */
retain diff reversion 0;
retain skip; /* 1=skip next rows <<<<< new line added */
qft = lowcase(qft);

if first.id then do;
diff=.; skip=0;
if qft = 'positive' then do; type=2; pos1d = t0; flag=2; end; else
if qft = 'negative' then do; type=1; flag=1; end;
else do; type = 0; flag=.; end;
end; else
if skip = 0 then do; /* <<<<< new line added */
if qft = 'positive' then do;
if type ne 2 /* 1st positive which is not 1st in ID */
and diff=.
then do; type=2; pos1d = t0; flag=.; end;
else if flag=2 then do;
diff=t0-pos1d; t2=t0;
end;
/* if type=2 no change need */
end; else
if qft = 'negative' then do;
if type =2 /* and diff=. /* 1st negative following positive(s) */
then do; diff = t0 - pos1d; t2=t0; flag=.; reversion=1; skip=1; end;
/* if type is 1 or 0 no change need */
end;
else do;
if type =2 /* missing following positive(s) */
then do; reversion=0; flag=.; type=0; skip=1; end;
end;
end; /* <<<<< new line added */

if last.id then do;
if flag=1 then do; diff=.; t1=.; t2=.; end; else
if flag=2 then do; diff=t0 - pos1d; t1=pos1d; t2=t0; end;
else do;
if diff > 0
then do; t1=pos1d; /* reversion diff and t2 retained */ end;
else do; diff=.; t1=.; t2=.; end;
end;
output;
end;
format t1 t2 date9.;
run;

data alg2x;
set alg2;
if flag=1 then reversion=0;/*all negatives*/
if flag=2 then reversion=0;/*all positives*/
if t1=. and t2=. then reversion=0;
run;

/*I added the last part to fit the algorithm*/

[Shmuel]

Where is the problem ?

Isn't DIFF the period of staying POSITIVE ?

[ahhh]

I edited the code and it works now. Thank you ..