I have a dataset where i'm trying to determine fallout/ outliers from a previous year. Here is an example of the table below:
Member
YR
A1
A2
A3
123
2019
1
0
1
123
2020
1
0
1
124
2019
1
1
0
124
2020
0
1
0
125
2019
0
1
1
125
2020
1
1
1
126
2019
0
1
1
126
2020
0
0
1
The A1-A3 are hierarchy based, A1 being most important and A3 being least. I'm trying to identify members and their corresponding A1 - A3 that didn't match from the previous year. So an expected output would be something like below:
Member
YR
A1
A2
A3
124
2020
Missing
Found
NoMatch
126
2020
NoMatch
Missing
Found
The goal is to find the A1-A3 variable assigned to a member that's missing, matched or has No Match on the previous year. In this case I'm looking at 2019 and matching it to 2020, so I only need 2020 rows/columns back.
I've tried building arrays and transposing the data with incld and excld columns for each year to find the missing or Nomatches and i cant seem to get the desired output. Any help would be appreciated.
... View more