Check all prior values and leading values within a group to create a flag variable

[gsk]

I need to check both "prior ever" and "leading ever" values.

That is, if any of the prior visits' values satisfy some criteria per subject id, we need to keep that in mind within the subject that the criteria is satisfied. Then, if we have some criteria satisfied in one of the prior visits and if all of the following values have a certain value (answer='N') for the subject for the rest of the visit, we need to create a flag variable to mark that. 

For example, this is what I have: 

data example;
input id visit answer $ score ;
datalines;
1 1 Y 10
1 2 N 5
1 3 N 3
1 4 N 2
1 5 N 9
1 6 N 11
1 7 Y 2
2 1 Y 3
2 2 N 5
2 3 Y 1
2 4 N 2
2 5 N 4
3 1 N 3
3 2 N 5
3 3 Y 2
3 4 Y 1
3 5 N 2
3 6 N 3
3 7 N 2
3 8 Y 5
3 9 Y 4
3 10 N 1
3 11 Y 5
3 12 Y 2

;

Want is what I want. High is some helping variable I made to create the flag variable -- I don't need this variable. This checks if the visit has answer="N" and score>=5. I need a flag variable to mark this: check if any prior visit had answer="N" and score>=5 for the subject ID. If yes (a prior visit had that), and if also "all" of the following visits have answer='N', then mark the flag = 'Y'.

data want ;
input id visit answer $ score high $ flag $;
datalines;
1 1 Y 10 N N
1 2 N 5 Y N
1 3 N 3 N Y
1 4 N 2 N Y
1 5 N 9 Y Y
1 6 N 11 Y Y
1 7 N 2 N Y
2 1 Y 3 N N
2 2 N 5 Y N
2 3 Y 1 N Y
2 4 N 2 N Y
2 5 N 4 N Y
3 1 N 3 N N
3 2 N 5 Y N
3 3 Y 2 N N
3 4 Y 1 N N
3 5 N 2 N N
3 6 N 3 N N
3 7 N 2 N N
3 8 Y 5 N N
3 9 Y 4 N N
3 10 N 1 N N
3 11 Y 5 N N
3 12 Y 2 N N
;

The first line: there is no prior visit for subject ID=1, so flag='N'

The second line (1 2 N 5 Y N): We have answer='N' and score >= 5, but the prior visit (visit=1) doesn't have answer='N' and score 5. So, the flag='N'

The third line (1 3 N 3 N Y): We have answer='N' and score >= 5 for visit=2, which is the visit=3's prior visit. So, this criteria is satisfied. Also, all following visits (visit 4, 5, 6, etc.) have answer='N'. Therefore, the flag='Y'. 

The fourth to seventh line all have flag='Y' for subjid = 1. 

11th line: subject id=2 at visit=3 has a prior visit with answer='N' and score>=5 at its visit 2. And, it has answer='N' for visit 4 and 5, which are all of its following visits, so the flag ='Y' at visit 3. 

For subject id=3, it has prior visit criteria satisfied; however, not all of the following visits have answer='N' at any point, so the flag is all 'N'

Thank you! 😞 Let me know if you need any clarification. 

[Shmuel]

It is difficult to me follow the conditions for assigning flag.

You are invited to run next code and correct it:

data example;
  input id visit answer $ score ;
datalines;
1 1 Y 10
1 2 N 5
1 3 N 3
1 4 N 2
1 5 N 9
1 6 N 11
1 7 Y 2
2 1 Y 3
2 2 N 5
2 3 Y 1
2 4 N 2
2 5 N 4
3 1 N 3
3 2 N 5
3 3 Y 2
3 4 Y 1
3 5 N 2
3 6 N 3
3 7 N 2
3 8 Y 5
3 9 Y 4
3 10 N 1
3 11 Y 5
3 12 Y 2
; run;

data want;
 set example;
  by id;
     retain pior_answer prior_score ;
     pior_answer = lag(answer);
     prior_score  = lag(score);
     if first.id then flag='N'; 
     else do;
        if answer='N' and score >= 5 then do;
           if pior_answer = 'N' and prior_score = 5
           then flag = 'N'; 
           else flag = 'Y'; 
        end; else
        if answer='N' then flag = 'Y';
    end;
run;

[hhinohar]

Here is a quick sample.

data have;
	input id visit answer $ score;
	datalines;
1 1 Y 10
1 2 N 5
1 3 N 3
1 4 N 2
1 5 N 9
1 6 N 11
1 7 Y 2
2 1 Y 3
2 2 N 5
2 3 Y 1
2 4 N 2
2 5 N 4
3 1 N 3
3 2 N 5
3 3 Y 2
3 4 Y 1
3 5 N 2
3 6 N 3
3 7 N 2
3 8 Y 5
3 9 Y 4
3 10 N 1
3 11 Y 5
3 12 Y 2
;
run;

data want;
	merge have have(drop=id visit rename=(answer=lead_answer score=lead_score) 
		firstobs=2) end=eof;
	retain flag_y;
	lag_answer=lag(answer);
	lag_score=lag(score);

	if lag_answer="N" and lag_score>=5 and lead_answer="N" then
		do;
			flag_y="Y";
		end;
	else
		do;
			flag_n="N";
		end;

	if lag(id) ne id then
		flag_y="";

	flag=ifc(flag_y="Y", "Y", ifc(flag_n="N", "N", ""));
	keep id visit answer score flag;
run;