Query for a financial balance with a PROC SQL in SAS?

Question

I have two tables, first call it "transactions"

transaction_id	transaction_type	amount
101	1	50.00
102	2	25.00
103	3	35.00
104	2	15.00
105	1	60.00

and second call it "transaction_types"

id	description
1	Deposit
2	Retirement
3	Inner_transaction

The "id" column in the "transaction_types" table, corresponds with the "transsaction_id" in the first table

Suppose that the initial balance is $100,000.00. I should write a query to get the historical account balance. The table that I should show is this:

transaction_id	initial_balance	deposit	withdrawal	final_balance
101	100000	50.00		100050
102	100050		25.00	100025
103	100025			100025
...	...	...	...	...

I write this code in SAS using a PROC SQL:

TITLE "Balance Historico de la Wallet Empresarial";
PROC SQL;
    SELECT transaction_id,
           100000 AS initial_balance FORMAT=DOLLAR32.2,
           "" AS deposit,
           "" AS withdrawal,
           "" AS final_balance
    FROM _LIB.TRANSACTIONS;
QUIT;

But I don't know how fill the other columns.

Edit your question with tags for ONE database that you are actually using. Its obviously not MySQL. — danblack, Aug 01 '21 at 01:43
Are there really only 3 transaction types? Why do you need a separate table for that? — Tom, Aug 01 '21 at 03:13
Why would you want to do this in SQL instead of a DATA step? — Tom, Aug 01 '21 at 15:46

Parfait · Answer 1 · 2021-08-01T02:27:33.557

Consider conditional aggregation where you assign new columns conditionally and then aggregate by needed grouping. Use calculated clause to complete needed final balance arithmetic.

TITLE "Balance Historico de la Wallet Empresarial"; 

PROC SQL; 
   SELECT transaction_id
        , initial_balance FORMAT=DOLLAR32.2
        , SUM(deposit) AS deposit FORMAT=DOLLAR32.2
        , SUM(withdrawal) AS withdrawal FORMAT=DOLLAR32.2
        , initial_balance + 
          calculated deposit - 
          calculated withdrawal AS final_balance FORMAT=DOLLAR32.2
  FROM 
   (SELECT ts.transaction_id
         , 100000 AS initial_balance
         , CASE WHEN tt.description = 'Deposit' THEN ts.amount ELSE NULL END AS deposit
         , CASE WHEN tt.description = 'Withdrawal' THEN ts.amount ELSE NULL END AS withdrawal
         , CASE WHEN tt.description = 'Inner_balance' THEN ts.amount ELSE NULL END AS inner_balance
    FROM _LIB.TRANSACTIONS ts
    INNER JOIN _LIB.TRANSACTION_TYPES tt
       ON ts.transaction_type = tt.id
   ) sub
  GROUP BY transaction_id
         , initial_balance;
QUIT;

Alternatively, with SAS SQL dialect, use IFN for slightly shorter syntax:

  ...
  , IFN(tt.description = 'Deposit', ts.amount, .) AS deposit
  , IFN(tt.description = 'Withdrawal', ts.amount, .) AS withdrawal
  , IFN(tt.description = 'Inner_balance', ts.amount, .) AS inner_balance
  ...

Thank you very much Parfait, but I still have a question: How does the "Group by" statement work in this case, since if I only group by the "transaction_id" field I get the same result. I do not understand why? — Sergio Fernandez Porras, Aug 01 '21 at 05:51
Parfait, I can't make the initial_balance update according to the final_balance, could you help me? — Sergio Fernandez Porras, Aug 02 '21 at 00:12

Query for a financial balance with a PROC SQL in SAS?

1 Answers1

Linked