0

I have this table, call it "pre_report":

initial_balance deposit withdrawal final_balance
1000 50 0 .
1000 0 25 .
1000 45 0 .
1000 30 0 .
1000 0 70 .

I want create a code in SAS that updates the "final_balance" field, the "deposit" field adds to the balance and "withdrawal" subtracts, but at the same time changes the values of the "initial_balance" field, in such a way that my desired output be this:

initial_balance deposit withdrawal final_balance
1000 50 0 1050
1050 0 25 1025
1025 45 0 1070
1070 30 0 1100
1100 0 70 1030

I try this:

proc sql;
    select initial_balance format=dollar32.2,
           deposit format=dollar32.2,
           withdrawal format=dollar32.2,
           sum(initial_balance,deposit,-withdrawal) as final_balance,
           calculated final_balance as initial_balance
    from work.pre_report;   
quit;

But it doesn't work properly. This code create two fields "final_balance" and "initial_balance" but both with the sames quantity.

code for creating "pre_report" table

data work.pre_report;
input initial_balance deposit withdrawal final_balance;;
datalines;
1000 50 0 .
1000 0 25 .
1000 45 0 .
1000 30 0 .
1000 0 70 .
run;

I would really appreciate if you help me.

Sergio Fernandez Porras
  • 11
  • 4
  • `proc sql` is not the best way to do this. A data step with `retain` is much more efficient. – Gordon Linoff Aug 01 '21 at 22:30
  • It's school work and they asked me to do it with SQL language, and I learned SQL within SAS. – Sergio Fernandez Porras Aug 01 '21 at 23:33
  • This appears to do what you want, but you'll need to work out what it's saying. You basically need to do a running total. https://stackoverflow.com/questions/54939511/proc-sql-running-total. But it's important to note: there is no indicator in your data of what order this needs to be summed in. There's no month or year – Nick.Mc Aug 02 '21 at 00:06
  • https://stackoverflow.com/questions/68606584/query-for-a-financial-balance-with-a-proc-sql-in-sas Nick.McDermaid this is my original question. I resolve only a part of my problem – Sergio Fernandez Porras Aug 02 '21 at 00:37
  • Are you taking a class in how to use SAS? Or are you trying to learn SQL? If the former use a data step. If the later then SAS is not the right SQL implementation for this type of problem as it does not support modern SQL syntax enhancements that would make it possible. – Tom Aug 02 '21 at 00:56

0 Answers0