Complex indexing using retain statement

[rosegarden81]

Hello all,

I have this data which looks quite complex to complete coding in a few data steps. My current code is taking me 500 lines of code and I think there is a better way to make it much short using retain statements that I am not getting to work.

The pic below (and the same in the attached excel file) shows what I am trying to produce (i.e. from columns G to I)

So I have visits level data with two patients in this example; the dates of their examination is shown in column C and 'Code' column shows what code was used in the claims. 

I am having to identify visits which have completed the exam if they follow a logic (in three ways)

Logic1:

As shown in column G, Appearance of most recent occurrence of code A0001 (if two consecutive visits have same code A0001) and then within 6 months of that date, an appearance of Relevant code (which could be D0239 for example).

So, for patient 1, he had two consecutive A0001 (5th Jan 2019 and 12th Jan 2019 resp) so I am finding any visits from 12th Jan 2019 onward that might have relevant codes within 6 months from that date (so from 12th Jan 2019 to ~12th July 2019). For this patient, the relevant code appears on 16th Jan 2019, 18th Jan, 16th Feb and 23rd June 2019. So all visits from 12th Jan 2019 through 23rd June are flagged as "Completed per criteria 1" in column G. A0001 has to appear first. 

Logic 2:

As per column H, Including all A0001 (and not just the most recent one in case they appear in consecutive visits) and follow same logic to flag any visits within 6 months from 1st occurrence of A0001. So 6 months from 5th Jan 2019.

Logic 3:

As per column I, flag only visits with most recent occurrence of A0001 (in case they appear one after another) and flag till the first occurrence of relevant code i.e. till 16th Jan as long as it is within 6 months from the date of A0001

Patient 2 has some non relevant codes too so the example shows the flagging accordingly. i.e. just ignore any non relevant codes.

If a visit does not follow the norms above, then it is flagged 0..

Any advice on how to go about this in short easy steps may be with retain statement etc would be very much appreciated!!!!!

Regards

Tina

[ballardw]

It is usually a very good idea to include the data that you start with. If you want code that has a chance of running then best is to provide example starting data in the form of a data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Replace any possibly sensitive values such as patient identifier with sequence number as you did for your question.

As shown in column G, Appearance of most recent occurrence of code A0001 (if two consecutive visits have same code A0001) and then within 6 months of that date, an appearance of Relevant code (which could be D0239 for example).

What date is "that date"? The "most recent" the first or last of the dates with the A0001 code? Then do what with this information?

I find it even harder to follow the logic or results for Logic 2 and 3.

Please provide descriptions of the RULES involved in the logic such as "if any date has codes x that is (relationship) to a (rule for setting the comparison date). Instructions like "per column H" means we have to try to derive the rules involved. You know what you are attempting and leave out details because it is likely obvious to you. We however do not know anything about the history or your data, the contents or implied rules.

It might be a good idea to paste your current code into a code box opened with the forum's {I} or "running man" icon. That way we could see what changes to existing code might be possible.