I have table in SAS Enterprise Guide like below.
Data types and meaning:
- ID - numeric - ID of client
- DT - date - date of change
- OFFER_1 - charcter - current offer
- OFFER_2 - character - offer after change
Values in original dataset are not sorted, but it can be sorted if it is important for solution.
ID | DT | OFFER_1 | OFFER_2
-----|-----------|----------|----------
123 | 01MAY2020 | PR | PR
123 | 05MAY2020 | PR | P
123 | 10MAY2020 | P | P
123 | 11MAY2020 | P | P
123 | 20MAY2020 | P | PR
123 | 21MAY2020 | PR | M
123 | 25MAY2020 | M | M
777 | 30MAY2020 | PR | M
223 | 02JAN2020 | PR | PR
223 | 15MAR2020 | PR | PR
402 | 20MAR2020 | M | M
33 | 11AUG2020 | M | PR
11 | 20JAN2020 | PR | M
11 | 05FEB2020 | M | M
And I need to create new column "COL1" where will be information:
- If client change offer from PR to P or M then count how many maximum days he lasted before returning to PR again, or how many days he is already on P or M after the change, if he has not returned to PR
- If he did not change PR to P or M "COL1" = 0
So, as a result I need something like below:
ID | DT | OFFER_1 | OFFER_2 | COL1
-----|-----------|----------|----------|---------
123 | 01MAY2020 | PR | PR | 15
123 | 05MAY2020 | PR | P | 15
123 | 10MAY2020 | P | P | 15
123 | 11MAY2020 | P | P | 15
123 | 20MAY2020 | P | PR | 15
123 | 21MAY2020 | PR | M | 15
123 | 25MAY2020 | M | M | 15
777 | 30MAY2020 | PR | M | 1
223 | 02JAN2020 | PR | PR | 0
223 | 15MAR2020 | PR | PR | 0
402 | 20MAR2020 | M | M | 0
33 | 11AUG2020 | M | PR | 0
11 | 20JAN2020 | PR | M | 16
11 | 05FEB2020 | M | M | 16
Because:
- ID = 123, has 15 in "COL1" - because changed PR to P or M and lasted a maximum of 15 days (from 05MAY2020 till 20MAY2020) before return to PR again, changed PR to P or M 2 times but the 2nd time the change lasted 4 days and 15 > 4
- ID = 777, has 1 in "COL1" - because changed PR to P or M and lasted a maximum of 1 day (30MAY2020)
- ID = 223, has 0 in "COL1" - because he did not change PR to P or M
- ID = 402, has 0 in "COL1" - because he did not change PR to P or M
- ID = 33, has 0 in "COL1" - because he did not change PR to P or M
- ID = 11, has 16 in "COL1" - because changed PR to P or M and lasted a maximum of 16 days (from 20JAN2020 till 05FEB2020) and did not return to PR again
How can I do that in SAS Enterprise Guide in PROC SQL or in normal SAS code ?