BookmarkSubscribeRSS Feed
Viresh
Calcite | Level 5

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

 

2 REPLIES 2
LinusH
Tourmaline | Level 20

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
Viresh
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 681 views
  • 0 likes
  • 2 in conversation