DATA Step, Macro, Functions and more

Matching Obs

Reply
New Contributor
Posts: 2

Matching Obs

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_NbrSec_IDMemo_Issue_DtAGCY_NbrCTRYMemo_Amt_USDRMRKSCRSP_MthdCRSP_TopicDue_DtLst_Updt_GTsEmp_Nbr_Lst_UpdtStatusLogDateLogTimeCompDateCompTime
0060060062777VOIA25Jul201612345678AU100.00my remarksAGY EMAILName11Nov201602Nov2016 13:58:59123456700OPEN02Nov201613:58:59  
0060060062777VOIA25Jul201612345678AU100.00my remarks12DISPUTE RESPMEMO VALID 09Nov2016 15:19:36123456700Responded  09Nov201615:19:36
0060060062777VOIA25Jul201612345678AU100.00my remarks123AGY EMAILName21Nov201610Nov2016 16:06:08123456700OPEN10Nov201616:06:08  
0060060062777VOIA25Jul201612345678AU100.00my remarks1234DISPUTE RESPMEMO VALID 14Nov2016 21:02:17124536700Responded  14Nov201621:02:17

 

Super User
Posts: 5,426

Re: Matching Obs

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...

Data never sleeps
New Contributor
Posts: 2

Re: Matching Obs

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.

Ask a Question
Discussion stats
  • 2 replies
  • 92 views
  • 0 likes
  • 2 in conversation