Hi Folks,
I need some help with this vertical data. I am kind of stuck here. This is how my data looks like
ID Date Start of Next date Response Gap Result
1 06MAR2017 08MAY2017 3 63 3
1 08MAY2017 26MAY2017 3 18 3
1 26MAY2017 30JUN2017 3 35 6
1 30JUN2017 25SEP2017 3 87 6
1 25SEP2017 14DEC2017 5 80 5
1 14DEC2017 12MAR2018 3 88 3
1 12MAR2018 3 6
1) The end goal is to derive the last column "Result" .
2) Gap is the duration in days between the second date(08MAY2017 ) - first date(06MAR2017) and so forth.
3) First row==> The gap>42 in the first row with a response of 3 followed by an another response of 3 with a gap>42 is a confirmation , then we can say the result is 3. It does not have to in an order but it has to be before 5. Any time you have 5 the result is automatically 5. In the first row the gap is 83 and response is 3 , then we look at row 2 and 3 , the condition fails because the gap<42 then condition satisfies at row 4 . Then we say the result is 3.
4) Second row ==> confirmation happens on row 4 . So , the result is 3.
5) Third row==> the response is 3 we have to look for the next response 3 which has also has to be before response 5 with gap >42. The second confirmation is not there , because row 3 and row 4 fails and we reach 5 at row 5, so the result will be set to 6.
5) Third row ==> No confirmation , so we set to 6 as well
6) Fourth row ==> No confirmation, so the we set to 6 as well.
7) Fifth row ==> When you have 5 , the result is 5 .
😎 Sixth row==> This has confirmation from seventh row.
9) Seventh row ==> No confirmation , so we set it to 6.
It is kind of little complicated . Hope some one can help with the SAS code or please advice the correct technique I can implement to achieve this result.
Thanks to all in advance for your help.
... View more