Assigning proper groups for Vertical data

[satkum33]

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.

[andreas_lds]

Can you provide your input dataset in usable form: as data-step using datalines?

[satkum33]

Hi Andreas,

                Here are the datalines,

data test;

input ID STDT: date9. RESPONSE GAP RESULT;
FORMAT stdt DATE9.;
datalines ;

1 06MAR2017 3 63 3
1 08MAY2017 3 18 3
1 26MAY2017 3 35 6
1 30JUN2017 3 87 6
1 25SEP2017 5 80 5
1 14DEC2017 3 88 3
1 12MAR2018 3 . 6
2 19APR2017 2 33 2
2 22MAY2017 3 56 3
2 17JUL2017 2 57 2
2 12SEP2017 2 56 2
2 07NOV2017 2 56 2
2 02JAN2018 2 56 2
2 27FEB2018 2 84 2
2 22MAY2018 2 84 2
2 14AUG2018 2 84 3
2 06NOV2018 3 . 6
;
run;

The iteration should yield the last column called Result.

Thanks for your help.