Assigning proper groups for Vertical data

satkum33 — Wed, 13 Feb 2019 05:27:58 GMT

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.

Re: Assigning proper groups for Vertical data

andreas_lds — Wed, 13 Feb 2019 06:26:24 GMT

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

Re: Assigning proper groups for Vertical data

satkum33 — Wed, 13 Feb 2019 14:26:55 GMT

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.

topic Assigning proper groups for Vertical data in SAS Programming

Assigning proper groups for Vertical data

Re: Assigning proper groups for Vertical data

Re: Assigning proper groups for Vertical data