topic Re: Matching Obs in SAS Programming

Matching Obs

Viresh — Mon, 10 Apr 2017 14:17:53 GMT

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

100.00

my remarks

AGY EMAIL

Name

11Nov2016

02Nov2016 13:58:59

123456700

OPEN

02Nov2016

13:58:59

0060060062777

VOIA

25Jul2016

12345678

100.00

my remarks12

DISPUTE RESP

MEMO VALID

09Nov2016 15:19:36

123456700

Responded

09Nov2016

15:19:36

0060060062777

VOIA

25Jul2016

12345678

100.00

my remarks123

AGY EMAIL

Name

21Nov2016

10Nov2016 16:06:08

123456700

OPEN

10Nov2016

16:06:08

0060060062777

VOIA

25Jul2016

12345678

100.00

my remarks1234

DISPUTE RESP

MEMO VALID

14Nov2016 21:02:17

124536700

Responded

14Nov2016

21:02:17

Re: Matching Obs

LinusH — Mon, 10 Apr 2017 14:25:30 GMT

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

Re: Matching Obs

Viresh — Mon, 10 Apr 2017 14:28:49 GMT

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.