04-10-2017 10:17 AM
In the below table I am trying to match "Open" date with "Responded" Date. For ex. Open=02Nov2016, I am trying to move Responded=09Nov2016 in the first line, so I can have what date a adispute was opened and what date it was CompDate(Completed) on the same row.
I have tried all types of merges and updates.
|0060060062777||VOIA||25Jul2016||12345678||AU||100.00||my remarks||AGY EMAIL||Name||11Nov2016||02Nov2016 13:58:59||123456700||OPEN||02Nov2016||13:58:59|
|0060060062777||VOIA||25Jul2016||12345678||AU||100.00||my remarks12||DISPUTE RESP||MEMO VALID||09Nov2016 15:19:36||123456700||Responded||09Nov2016||15:19:36|
|0060060062777||VOIA||25Jul2016||12345678||AU||100.00||my remarks123||AGY EMAIL||Name||21Nov2016||10Nov2016 16:06:08||123456700||OPEN||10Nov2016||16:06:08|
|0060060062777||VOIA||25Jul2016||12345678||AU||100.00||my remarks1234||DISPUTE RESP||MEMO VALID||14Nov2016 21:02:17||124536700||Responded||14Nov2016||21:02:17|
04-10-2017 10:25 AM
Can you be certain that the data always come sin pair LogDate/CompDate (and with no LogDate between)?
If so, the easiest to move the LogDate to the CompDate row, given the existing sort order.
Use a data step with RETAIN on variables that help you identify id and values from the previous row you wish to cascade.
If you wish to move "up" a value, resort the data first in descending order.
The ideal situation would be that the Log event had an id that was present on the Comp event, but I assume that isn't available from the source...
04-10-2017 10:28 AM
LinusH, thanks for your reply, the data comes pairs, sometimes in a pair of 2's sometimes in a pair of 4's. For example in the first 4 rows, the initial dispute came in on the 2nd of Nov which was responded on the 9th of Nov. The the dispute came back again with the same Memo number on the 10th and was responded on the 14th.