BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hoaglam
Calcite | Level 5

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_CDTRAN_AMTDEBIT_CREDIT_INDICATORnew_key
77640.00C109560357322350000000.00
77640.00C109560357322350000000.00
77640.00C109560357322350000000.00
77640.00C109560357322351000000.00
35315000.00D109560345122351000000000.00
75315000.00C109560345122351000000000.00
75315000.00C109560345122351000000000.00
77629.00C1095603451223510000000.00
77640.00C1095603451223510000000.00
77640.00C1095603451223510000000.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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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.

View solution in original post

8 REPLIES 8
ballardw
Super User

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

hoaglam
Calcite | Level 5

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!

ballardw
Super User

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

hoaglam
Calcite | Level 5
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.
Reeza
Super User
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.
hoaglam
Calcite | Level 5
Thanks, I'll tinker with this concept to see if I can make some progress.
Reeza
Super User
Is that a type on your 5th record then, with the TRAN_CD being 353 rather than 753?
hoaglam
Calcite | Level 5
Yes, that 353 is a transaction code that means "debit", which is also represented as a "D" in the "Debit Credit Indicator" column.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 519 views
  • 1 like
  • 3 in conversation