Thanks @mkeintz , but is there a possible solution to make this code work even if the data is not sorted ? And will this code work if I can get my data sorted, say on customer number alone or with any other variable in combination ?
... View more
No, the real time data is not sorted as I mentioned that it is on a transaction level. And I tried sorting the data on customer and run the code provided above but getting below error in the log. if last.txn_amt=0 and dhms(nxt_date,,nxt_time) - dhms(txn_date,,txn_time)<='02:00:00't then ERROR 159-185: Null parameters for DHMS are invalid.
... View more
@mkeintz Thanks for taking a look at my query. Actually the data is at transaction level in which the dedupable records might not be consecutive always.
... View more
@japelin Variable txn_time is the exact time at which the transaction took place and to identify a single transaction I have txn_id column in my dataset. I have edited my post and added txn_id in my sample data and desired output table.
... View more
In SAS, I have a transaction data and I need to dedup it in such a way that if a customer has attempted multiple transactions of the same amount with the same merchant within 2 hours then I should get the latest instance based on transaction time in my final dataset, but if the transaction time is greater than 2 hours then I should get all the records. Below is my sample data. cust_nbr txn_id merchant txn_amt acct_num txn_date txn_time 11111111111111111111 A1234 XYZ 1000 0123456789 17Jan2021 23:59:57 11111111111111111111 B9021 XYZ 1000 0123456789 18Jan2021 0:00:03 11111111111111111111 V7619 XYZ 2000 0123456789 18Jan2021 0:00:08 11111111111111111111 W8911 XYZ 2000 0123456789 18Jan2021 0:00:20 22222222222222222 J0569 ABC 3000 9876543210 17Jan2021 8:00:02 22222222222222222 T2801 ABC 3000 9876543210 18Jan2021 9:00:07 And below is my desired output. cust_nbr txn_id merchant txn_amt acct_num txn_date txn_time 11111111111111111111 B9021 XYZ 1000 0123456789 18Jan2021 0:00:03 11111111111111111111 W8911 XYZ 2000 0123456789 18Jan2021 0:00:20 22222222222222222 J0569 ABC 3000 9876543210 17Jan2021 8:00:02 22222222222222222 T2801 ABC 3000 9876543210 18Jan2021 9:00:07 I am guessing I need to do some kind of conditional deduping based on transaction date and time. Any help is appreciated ! @Reeza @Ksharp @Kurt_Bremser @Tom @MichelleHomes
... View more