topic Re: Subracting rows where duplicate values are present in a column in SAS Enterprise Guide

Subracting rows where duplicate values are present in a column

hoaglam — Tue, 20 Apr 2021 14:05:38 GMT

I've been working on a solution to subtracing duplicate credits from a ledger. Is anyone able to lend a hand on this? Sample data below:

TRAN_CD	TRAN_AMT	DEBIT_CREDIT_INDICATOR	new_key
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322351000000.00
353	15000.00	D	109560345122351000000000.00
753	15000.00	C	109560345122351000000000.00
753	15000.00	C	109560345122351000000000.00
776	29.00	C	1095603451223510000000.00
776	40.00	C	1095603451223510000000.00
776	40.00	C	1095603451223510000000.00

In the case of the $15000 transaction that occurred three times, I would want to basically negate the debit and one of the credits to show a net $15000 transaction, not $45000 worth of transactions. I concatenated a few feilds within the data to create a unique key that you see in the far right column. Very stuck. Any help is appreciated!

Re: Subracting rows where duplicate values are present in a column

ballardw — Tue, 20 Apr 2021 14:15:29 GMT

@hoaglam wrote:

I've been working on a solution to subtracing duplicate credits from a ledger. Is anyone able to lend a hand on this? Sample data below:

TRAN_CD TRAN_AMT DEBIT_CREDIT_INDICATOR new_key

776 40.00 C 109560357322350000000.00

776 40.00 C 109560357322350000000.00

776 40.00 C 109560357322350000000.00

776 40.00 C 109560357322351000000.00

353 15000.00 D 109560345122351000000000.00

753 15000.00 C 109560345122351000000000.00

753 15000.00 C 109560345122351000000000.00

776 29.00 C 1095603451223510000000.00

776 40.00 C 1095603451223510000000.00

776 40.00 C 1095603451223510000000.00

In the case of the $15000 transaction that occurred three times, I would want to basically negate the debit and one of the credits to show a net $15000 transaction, not $45000 worth of transactions. I concatenated a few feilds within the data to create a unique key that you see in the far right column. Very stuck. Any help is appreciated!

And just how are we to tell which are duplicates?

From what you are showing so far I would not even know that debit you reference is in any way related to the credit value.

I would guess that there is more identification information that you are not providing that is very likely going to be important for actually having a chance to program a solution.

Re: Subracting rows where duplicate values are present in a column

Reeza — Tue, 20 Apr 2021 14:21:55 GMT

Mark Credits as -1 and Debits as +1 in a column called direction, and then group by your identifier columns and sum the transaction amounts multiplied by the direction.
You may want to flip debits/credits positive/negative to get the sign you need. Not sure which fields will uniquely identify a record since your first three look identical to me.

Re: Subracting rows where duplicate values are present in a column

hoaglam — Tue, 20 Apr 2021 14:22:38 GMT

Hi there. In the data I'm showing the duplicates I'm trying to remove are in rows 5, 6 and 7. In reality, we have a net $15,000 transaction. All three of these keys share the same "new_key" identifier, which is how I'm identifying duplicates. Hopefully thats helpful. Thanks again!

Re: Subracting rows where duplicate values are present in a column

Reeza — Tue, 20 Apr 2021 14:27:01 GMT

Is that a type on your 5th record then, with the TRAN_CD being 353 rather than 753?

Re: Subracting rows where duplicate values are present in a column

hoaglam — Tue, 20 Apr 2021 14:33:44 GMT

Yes, that 353 is a transaction code that means "debit", which is also represented as a "D" in the "Debit Credit Indicator" column.

Re: Subracting rows where duplicate values are present in a column

ballardw — Tue, 20 Apr 2021 14:49:29 GMT

@hoaglam wrote:

Hi there. In the data I'm showing the duplicates I'm trying to remove are in rows 5, 6 and 7. In reality, we have a net $15,000 transaction. All three of these keys share the same "new_key" identifier, which is how I'm identifying duplicates. Hopefully thats helpful. Thanks again!

You have three 109560357322350000000.00 for the "new_key" in the first three records. Why are they not "duplicates"? Same with 1095603451223510000000.00 for the last three. Why are they not duplicates.

I think that your problem description is a bit incomplete or you have multiple meanings for "duplicate".

Re: Subracting rows where duplicate values are present in a column

hoaglam — Tue, 20 Apr 2021 14:56:59 GMT

Im using that unique key as a grouping only. The duplicates that I'm worried about are in the transaction values where both debits AND credits are present within the same account for the same amount. In the table I posted, the only duplicate I posted was for row 5. Row 5 should negate row 6 in this case, leaving a single transaction for that "new_key" group.

Re: Subracting rows where duplicate values are present in a column

hoaglam — Tue, 20 Apr 2021 14:58:03 GMT

Thanks, I'll tinker with this concept to see if I can make some progress.