是否在SAS中使用Proc SQL查询财务余额？ [英] Query for a financial balance with a PROC SQL in SAS?

问题描述

我有两个表，第一个称为&Quot；Transaction&Quot；

transaction_id	TRANSACTION_TYPE	金额
101	1	50.00
102	2	25.00
103	3	35.00
104	2	15.00
105	1	60.00

第二个名称为"；Transaction_Types&Quot；

id	说明
1	押金
2	退休
3	INTERNAL_TRANSACTION

表中的"；id"；列与第一个表中的"；transsaction_id"；列相对应

假设初始余额为100,000.00美元。我应该写一个查询来获得历史账户余额。我应该显示的表格如下：

transaction_id	Initial_Balance	存放	撤销	FINAL_BALANCE
101	100000	50.00		100050
102	100050		25.00	100025
103	100025			100025
.	.	.	.	.

我使用proc SQL在SAS中编写此代码：

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;

但我不知道如何填写其他栏。

推荐答案

考虑条件聚合，其中您有条件地分配新列，然后按需要的分组聚合。使用calculated子句完成所需的最终余额运算。

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; 

或者，对于SAS SQL方言，使用IFN表示稍短的语法：

  ...
  , 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
  ...

这篇关于是否在SAS中使用Proc SQL查询财务余额？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！