topic Re: Same account with debit and credits done. in SAS Programming

Same account with debit and credits done.

MJM11111 — Tue, 19 Dec 2023 15:31:04 GMT

Hi, I have a data set where there is incoming and outgoing transactions happening based on transaction codes. I want to use this data Set to see if I look at transaction codes 401,395,11 and 71 I know it's all credits and transaction codes 402, 911 and 394 it's all debit that went through on the account. I need to build an output where the account number had a debit done on the account and then that some account number went and did a credit (reversal) on the account. So I only want to see the account number if there was a debit done for an x amount and then if there was a reversal done for the same amount.

Hope someone can help me.

Re: Same account with debit and credits done.

PaigeMiller — Tue, 19 Dec 2023 15:36:08 GMT

Please provide some example data (as working SAS data step code — instructions — do not provide data as Excel files or screen captures), the data and IDs can be fake as long as they are a good representation of the actual problem.

Re: Same account with debit and credits done.

ballardw — Tue, 19 Dec 2023 15:49:38 GMT

You will have to very carefully describe exactly how we can tell a "reversal" has been done based on the content of the data. That may well mean you have to explain what each of those codes you discuss actually means. Otherwise we have no way of telling when a "debit" that is the same as a "credit" is not just happenstance.

Really, example data is going to be needed and quite possibly more clearly defined rules based on actual values that appear, such as the codes, in the data.

Re: Same account with debit and credits done.

A_Kh — Tue, 19 Dec 2023 17:10:09 GMT

Assuming we need to identify Accounts for which the same amount of debit and credit transactions done. First separate data into debit and credit transactions, then merge the same accounts based on the same amount.

*Separate data into debit and credit transactions; 
data debit credit; 
	set have; 
	if Transaction_Codes in (401, 395, 11, 71 /*more codes here*/) then output debit; 
	else if Transaction_Codes in (402, 911, 394 /*more codes here*/) then output credit; 
	keep  Account_Number Transaction_Codes Transaction_Amount; 
run; 

*Merge back debit and credit transactions: if the same Amount occured for both credit and debit of the same Account; 
proc sql;
	create table want (drop AN TC TA) as
		select* from debit as a
			left join (select Account_Number as AN, Transaction_Codes as TC, Transaction_Amount as TA from credit) as b
		on a.Account_Number=b.AN and a.Transaction_Amount=b.TA
	order by Account_Number;
quit;