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
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;