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;
... View more