I am new to SAS community and need your help for one of my analysis. I have a table like below:
ID VAR | Group | Breach | Month |
---|---|---|---|
1 | A | 1 | 202001 |
2 | A | 1 | 202002 |
3 | A | 1 | 202003 |
4 | A | 1 | 202004 |
5 | A | 1 | 202005 |
6 | A | 1 | 202006 |
7 | A | 0 | 202007 |
8 | A | 1 | 202008 |
9 | A | 1 | 202009 |
10 | A | 0 | 202010 |
11 | A | 1 | 202011 |
12 | A | 0 | 202012 |
13 | A | 1 | 202101 |
14 | A | 1 | 202102 |
15 | A | 0 | 202103 |
16 | A | 1 | 202104 |
17 | A | 1 | 202105 |
18 | A | 1 | 202106 |
19 | A | 1 | 202107 |
20 | A | 0 | 202108 |
21 | A | 1 | 202109 |
I want only to select rows which has 2 consecutive breaches within a group A , only once in a block of consecutive 0’s or 1’s. So basically I want the following table as output:
ID VAR | Group | Breach | Month | Result |
---|---|---|---|---|
1 | A | 1 | 202001 | No |
2 | A | 1 | 202002 | Yes |
3 | A | 1 | 202003 | No |
4 | A | 1 | 202004 | No |
5 | A | 1 | 202005 | No |
6 | A | 1 | 202006 | No |
7 | A | 0 | 202007 | No |
8 | A | 1 | 202008 | No |
9 | A | 1 | 202009 | Yes |
10 | A | 0 | 202010 | No |
11 | A | 1 | 202011 | No |
12 | A | 0 | 202012 | No |
13 | A | 1 | 202101 | No |
14 | A | 1 | 202102 | Yes |
15 | A | 0 | 202103 | No |
16 | A | 1 | 202104 | No |
17 | A | 1 | 202105 | Yes |
18 | A | 1 | 202106 | No |
19 | A | 1 | 202107 | No |
20 | A | 0 | 202108 | No |
21 | A | 1 | 202109 | No |
Thanks in Advance!!
Advertisement
Answer
Welcome 🙂
Try this
x
data have;
input IDVAR Group $ Breach Month;
datalines;
1 A 1 202001
2 A 1 202002
3 A 1 202003
4 A 1 202004
5 A 1 202005
6 A 1 202006
7 A 0 202007
8 A 1 202008
9 A 1 202009
10 A 0 202010
11 A 1 202011
12 A 0 202012
13 A 1 202101
14 A 1 202102
15 A 0 202103
16 A 1 202104
17 A 1 202105
18 A 1 202106
19 A 1 202107
20 A 0 202108
21 A 1 202109
;
data want;
set have;
by Group;
if Breach = 1 then c + 1;
if first.Group then c = 1;
if Breach = 0 then c = 0;
Result = ifc(c = 2, 'Yes', 'No');
run;