Output all Records if "vacrec=No, at three or more consecutive visits"

[PranayaG]

Could you please help me out,How to set up programming for below condition

Output all Records if "vacrec=No, at three or more consecutive visits"

subject  folder  folderseq  vecrec

1001     v1        1.00       No

1001      v2        2.00      No

1001      v3        3.00      Yes

1001      v4        4.00      No

1001       v5        5.00     No

1001       v6       6.00      No

1002      v1        1.00     Yes

1002      v2        2.00      No

1002      v3        3.00      Yes

1002      v4        4.00      No

1002       v5       5.00      No

1002       v6       6.00      No

1002      v7        7.00      No

Note that "Do not output if one or two consecutive visits reported as No"

[PaigeMiller]

---

@PranayaG wrote:

Output all Records if "vacrec=No, at three or more consecutive visits"

---

Does this mean, for subject 1001 that you want all six records output, or does this mean that for subject 1001 you want folder v4 v5 and v6 output but not v1 v2 v3?

Can you provide a data set where at least one subject doesn't have 3 consecutive No?

[PranayaG]

Yes we want records, for subject 1001 v4 v5 v6 and for subject 1002 v4 v5 v6 v7 as a output
Please find below dataset
subject folder folderseq vecrec
1001 v1 1.00 No
1001 v2 2.00 No
1001 v3 3.00 Yes
1001 v4 4.00 No
1001 v5 5.00 No
1001 v6 6.00 No
1002 v1 1.00 Yes
1002 v2 2.00 No
1002 v3 3.00 Yes
1002 v4 4.00 No
1002 v5 5.00 No
1002 v6 6.00 No
1002 v7 7.00 No
1004 v2 2.00 Yes
1004 v3 3.00 No
1004 v4 4.00 No
1004 v5 5.00 Yes
1004 v6 6.00 No

[PaigeMiller]

This seems to work. The variable CONSEC counts consecutive values of "No" and resets to zero whenever a "Yes" is found. Then SQL picks out the desired records.

data intermediate;
    set have;
    by subject;
    if first.subject or vecrec='Yes' then do; 
        consec=0;
        group+1;
    end;
    if vecrec='No' then consec+1;
run;
proc sql;
    create table want as select subject,folder,folderseq,vecrec
    from intermediate
    where vecrec='No'
    group by group
    having max(consec)>=3;
quit;

[PranayaG]

Thanks Paige !!!!
It's works.

[Ksharp · Accepted solution]

Same as Paige .

data have;
input (subject  folder  folderseq  vecrec) ($);
cards;
1001     v1        1.00       No
1001      v2        2.00      No
1001      v3        3.00      Yes
1001      v4        4.00      No
1001       v5        5.00     No
1001       v6       6.00      No
1002      v1        1.00     Yes
1002      v2        2.00      No
1002      v3        3.00      Yes
1002      v4        4.00      No
1002       v5       5.00      No
1002       v6       6.00      No
1002      v7        7.00      No
;

data temp;
 set have;
 by subject vecrec notsorted;
 group+first.vecrec;
run;
proc sql;
create table want as
select * from temp 
 where vecrec='No'
  group by group
   having count(*)>2;
quit;

[PranayaG]

Suppose as I want vice versa like if "vacrec=No, at one or two consecutive visits"  and Do not output if three or more consecutive visits reported as No.
Shall i need to do same?

[Ksharp]

having count(*)<3;

[PranayaG]

Thanks!!!