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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.