I have a big data with 100+ variables and I want to keep records depending on the "Incident" variable. For example, I want to keep records only were the incident variable during (week0) is not missing "test and/or "date" variable. But, If the incident variable is missing "test" and "date" values during (week0) then I want to replace (or keep) the "1_week0" value in place of the week0. Please look the table below as an example and the outcome.
ID
|
Incident
|
test
|
date
|
Result
|
1
|
Week0
|
34
|
2/2/2021
|
90
|
1
|
1_week0
|
40
|
3/4/2021
|
56
|
2
|
Week0
|
.
|
.
|
100
|
2
|
1_week0
|
84
|
5/12/2021
|
34
|
2
|
Week1
|
50
|
5/23/2021
|
67
|
3
|
Week0
|
43
|
3/8/2021
|
98
|
3
|
1_week0
|
10
|
4/21/2021
|
80
|
3
|
Week1
|
23
|
5/1/2021
|
45
|
4
|
week0
|
.
|
.
|
76
|
4
|
1_week0
|
64
|
3/7/2021
|
78
|
This is how I would want the outcome to look like
ID
|
Incident
|
test
|
date
|
Result
|
1
|
Week0
|
34
|
2/2/2021
|
90
|
2
|
1_week0
|
84
|
5/12/2021
|
34
|
3
|
Week0
|
43
|
3/8/2021
|
98
|
4
|
1_week0
|
64
|
3/7/2021
|
78
|