Transaction File With Multiple Payments For The Same Account

[nmck]

Hi - First post here and fairly new to SAS. I have found a lot of help on this board and hoping I can get some on this issue. I feel like I should be using first./last. by looking at other examples but my calculations aren't coming out correctly (or at all)

I have a transaction file with the payment_amt and adjustment_amt in two different fields. I am using a data step to combine these all into one column as I feel that would be easier to do the calculation. I am also given an EOD balance that I can compare to the balance currently in our system. Obviously, doing these calculation per line cause a large number of "balance exceptions"

How can I calculate the total adjustment & payment amount if there are multiple transactions for an account on different lines?

This is the dataset that I'm trying to do the calculations on (imported from a csv) - "newtrans_amt" is the amount I've combined PAYMENT_AMT and ADJUSTMENT_AMT into

data combinekeys;
set re_pay;
PAYMENT_AMT = tranwrd(PAYMENT_AMT, ",", "");
ADJUSTMENT_AMT = tranwrd(ADJUSTMENT_AMT, ",", "");
EOD_BALANCE = tranwrd(EOD_BALANCE, ",", "");
PAYMENT_AMT = compress(PAYMENT_AMT);
ADJUSTMENT_AMT = compress(ADJUSTMENT_AMT);
EOD_BALANCE = compress(EOD_BALANCE);
format newkey $10.;
if ADJUSTMENT_KEY ne "" then newkey = ADJUSTMENT_KEY;
IF PAYMENT_KEY ne "" then newkey = PAYMENT_KEY;
if ADJUSTMENT_AMT ne "" then newtrans_amt = ADJUSTMENT_AMT;
if PAYMENT_AMT ne "" then newtrans_amt = PAYMENT_AMT*-1;
run;

proc sort data=combinekeys; by CONSUMERNO; run;

From this point I'm kind of lost on how to:

-total the total transaction amount using "newtrans_amt" by CONSUMERNO
-If I can get a valid amount on in newtrans_amt, I should be able to take care of comparing that amount with what our system balance will be. to match with EOD_BALANCE. 

Any help is greatly appreciated.

[A_Kh · Accepted solution]

Hi @nmck , 

Here is my suggestion. 

For arithmetic calculations variables need to be in numeric format in general. From the screenshot and your example code it looks like your data stored in character format, plus you are manipulating data using character functions, which is not quite correct. 

To calculate Total of 2 or more columns,  we can use SUM function in data or sql step.  

NEWTRANS_AMOUNT=SUM(PAYMENT_AMT, ADJUSTMENT_AMT);

 To calculate total amount by group, the best is to use SUM function in PROC SQL along with GROUP BY clause. 

proc sql;
create table want as
select*, sum(newtrans_amount) as Total
from have
group by CONSUMERNO;
quit;

[nmck]

Thank  you - That part worked great. I need to flip the PAYMENT_AMT to a positive which is why you see the * -1 in the SUM() statement below. Now each row has a total of both payments.

data combinekeys;
set re_pay;
PAYMENT_AMT = tranwrd(PAYMENT_AMT, ",", "");
ADJUSTMENT_AMT = tranwrd(ADJUSTMENT_AMT, ",", "");
EOD_BALANCE = tranwrd(EOD_BALANCE, ",", "");
PAYMENT_AMT = compress(PAYMENT_AMT);
ADJUSTMENT_AMT = compress(ADJUSTMENT_AMT);
EOD_BALANCE = compress(EOD_BALANCE);
format newkey $10.;
if ADJUSTMENT_KEY ne "" then newkey = ADJUSTMENT_KEY;
IF PAYMENT_KEY ne "" then newkey = PAYMENT_KEY;
NEWTRANS_AMOUNT = SUM(PAYMENT_AMT*-1, ADJUSTMENT_AMT);

bal = "100.91";
run;

proc sort data=combinekeys; by CONSUMERNO; run;

proc sql;
create table want as
select*, sum(newtrans_amount) as Total
from combinekeys
group by CONSUMERNO;
quit;

However, I still need to make sure the balance in my system "bal" minus the new variable Total matches the EOD_BALANCE. How do I complete that since Total is on both rows? To test this. I've created a variable bal and set it equal to an EOD_BALANCE to test the calculation.

This seems to work, but the issue I'm having is that it is only outputting one of the payments and not both. As you can see from the screenshot below. I have 8 total transactions in the dataset 'Want' but only 5 total when combining 'doadjustment' and 'Balexception'

data newcalc balexception doadjustments ;
set want;
by CONSUMERNO;
if first.CONSUMERNO then do;
     if (BAL - Total) ne EOD_BALANCE then output balexception;
     else output doadjustments;
end;
if last.CONSUMERNO;
run;

[Tom]

These statements do not make any sense together.

PAYMENT_AMT = tranwrd(PAYMENT_AMT, ",", "");
ADJUSTMENT_AMT = tranwrd(ADJUSTMENT_AMT, ",", "");
PAYMENT_AMT = compress(PAYMENT_AMT);
ADJUSTMENT_AMT = compress(ADJUSTMENT_AMT);
NEWTRANS_AMOUNT = SUM(PAYMENT_AMT*-1, ADJUSTMENT_AMT);

Are PAYMENT_AMT character variables or numeric variables?

If they are character why did you convert the commas into spaces and then remove the spaces?  If you want to convert a string that has thousands separators into a number just use the COMMA informat.  It will ignore the commas.

NEWTRANS_AMOUNT = SUM(-1*input(PAYMENT_AMT,comma32.),input(ADJUSTMENT_AMT,comma32.));

If you want a running total by date then include date in the grouping.

If you want a running total then don't use SQL.  SQL processes observations as unordered sets.  Use a data step.

[nmck]

For me, it seemed I was trying to replace the comma with no space, but the space was showing up, that's why I was using compress. I have replaced it with what you recommended and that seems to be working fine. Thanks for that.

I'm not doing a running total by date. The first reply to my question got all the total payment by CONSUMERNO worked out. I'm just trying to compare the totals to my system balance as explained in my previous post.

[A_Kh]

It's hard to me figure out what the issue is, but if the missing observation solves the problem then my guess is you are sub-setting the data with 'if last.variable' condition. I think you get the desired number of observations if you remove:

if last.CONSUMERNO;

[nmck]

No, its still removes the observation if there are more than one transaction for an account. I've resorted to doing the below. It creates duplicates of observations that only have one transaction per account, but I'm just deduping by the "newkey" which is a unique id and that cleans it up just fine. So if anyone has any other ideas, I'm all ears. I appreciate the proc sql statement, exactly what I was looking for on that part.

data newcalc balexception doadjustments;
set want;
by CONSUMERNO;
if first. CONSUMERNO then do;
if (bal + Total) ne EOD_BALANCE then output balexception;
else output doadjustments;
end;
if last. CONSUMERNO then do;
if (bal + Total) ne EOD_BALANCE then output balexception;
else output doadjustments;
end;
run;