I am working on this complex query where I need to identify the organism for a patient and look for the same organism at the subsequent visits and flag the value if it's not there. More explanation is given after the data. Here is what data looks like: Actual Data: ID Visit Org1 Resolution1 Org2 Resolution2 Org3 Resolution3 Org4 Resolution4 1 1 121 Yes 220 No 225 No 301 No 1 2 220 Yes 301 No 1 3 1 4 So, in the data example above, there are 4 organisms reported for ID=1 at Visit=1 and 3 of these organisms did not resolve at this visit(subsequent resolution=no). I need to check if these unresolved organisms were reported at visit=2 or not. If they are not reported than that visit should be flagged with missing organism. In the example, organisms 220.225 and 301 were not resolved at the 1st visit and therefore should be checked at 2nd visit. Now at 2nd visit organisms 220 and 301 were reported but not 225 so the corresponding flag variable should report this organism. Also at the 2nd visit 301 was not resolved so we need to check if 301 was reported at 3rd visit. In the example, 301 was not reported at 3rd visit and therefore corresponding flag variable should report this. Here is the challenging part though: 1.) Each patient might have somewhere 1 to 9 visits and it varies from patient to patient. 2.) Each patient might have up to 9 organisms reported at any visits. 3.) The organism flag variable Org_Flag should have multiple organisms if query returns more than one missing organism at any visit. eg 220,225 for the same visit. Here is the data I want: ID Visit Org1 Resolution1 Org2 Resolution2 Org3 Resolution3 Org4 Resolution4 Org_Flag 1 1 121 Yes 220 No 225 No 301 No 1 2 220 Yes 301 No 225 1 3 301 1 4 Any help would be appreciated with this query.
... View more
