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.
Thanks,
I have tried all types of merges and updates.
Memo_Nbr | Sec_ID | Memo_Issue_Dt | AGCY_Nbr | CTRY | Memo_Amt_USD | RMRKS | CRSP_Mthd | CRSP_Topic | Due_Dt | Lst_Updt_GTs | Emp_Nbr_Lst_Updt | Status | LogDate | LogTime | CompDate | CompTime |
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 |
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...
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.
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.