两个数据集之间的SAS迭代循环 [英] SAS iterative loops between two datasets
问题描述
我遇到了两个数据集的情况.
I'm stuck with a situation where I have two datasets.
其中一个包含客户级别的默认记录，每行包括ID_customer，date_default，amount_default之类的列，以及一个等于amount_default的变量，我需要将其作为可编辑变量，我将其称为left_default
One containing defaults records at customer level, with each line including columns like ID_customer, date_default, amount_default, and a variable equal to amount_default which I need as editable variable which I call left_default
第二个包括客户级别(如果有)用来偿还这些违约的款项.该变量类似于默认值之一，例如ID_customer，date_payment，amount_payment和一个等于amount_payment的变量，我称之为left_paymnet
The second includes the payments, if any, made at customer level to repay these defaults. The variable are similar to the one of defaults, like ID_customer, date_payment, amount_payment and a variable equal to amount_payment which I need as ediatable which I call left_paymnet
我要实现的是代码迭代遍历默认表的每一行，并且对于每个默认记录，它都会在付款表中检查是否有同一位客户付款，且付款日期大于或等于为默认值之一.
What I want achive is that the code iteratively go through each lines of the default table, and for each default record it checks in the payment table if there are any payment made by the same customer, with the payment date greater or equal to the one of the default.
逻辑是最早的默认付款方式，优先于新的
The logic is oldeast default get paid with priority respect to newer
如果有符合上述条件的任何付款，则根据付款金额，我应该在默认表中填充新列，每次付款都要填充一列，直到完全覆盖了默认金额为止.
If there is any payment with the above mentioned criteria, then depending on the amount of the payment made then I should populate the default table with new columns, one for each payment until the default amount is fully covered.
同时，如果paymnet金额大于未支付的默认金额，那么我应该用剩余的部分欠款支付后剩余的付款金额来更新left_payment列.
At the same time, if the paymnet amount is greater than the default amount left unpaid, then I should update the left_payment column with the amount of payment left after paying the part of the remaining default.
我想举个例子更简单.
让我们说
table default
ID amount_default date_default left_defaullt
1 5 01012015 5
1 4 10012015 4
table paymnet
ID amoutn_payment date_paymnet left_paymnet
1 3 15122015 3
1 6 18012016 6
因此，在第一次迭代中，代码首先应关注第一个默认值5欧元，然后在付款表中查找付款应从第一条付款行中提取3欧元，从第二条付款行中提取2欧元.这样，在第一次迭代后，结果应该是
So at the first iteration the code first should focus on the first default of 5 euro, and then looking for payments in the payment table should retrieve 3 euro from the first line of payment and 2 euro from the second line of payment. So that after the first iteration the result should be
default
ID amount_default date_default left_defaullt payment1 datepayment1
1 5 01012015 2 3 15122015
1 4 10012015 4
payment
ID amoutn_payment date_paymnet left_paymnet
1 3 15122015 0
1 6 18012016 6
由于第一个违约未付清，因此第二次迭代应创建以下结果
As the first default is not fully paid the second iteration should create the following result
default
ID amount_default date_default left_defaullt pay1 date1 pay2 datet2
1 5 01012015 0 3 15122015 2 18012016
1 4 10012015 4
payment
ID amoutn_payment date_paymnet left_paymnet
1 3 15122015 0
1 6 18012016 4
作为第一个默认值(如果已全额支付)，则迭代将移至第二个默认值
as the first default if fully paid then the iteration move to the second default
ID amount_default date_default left_defaullt pay1 date1 pay2 datet2
1 5 01012015 0 3 15122015 2 18012016
1 4 10012015 0 4 18012016
payment
ID amoutn_payment date_paymnet left_paymnet
1 3 15122015 0
1 6 18012016 0
作为最终结果，我们知道何时支付每个违约的每个部分.
So that as final result we know when each part of each default has been paid.
这涉及SAS，我假设通过两个数据集进行两个do循环，但不确定如何将类似的代码放在一起，因为在网络上似乎没有太多的文档.
This involve in SAS I suppose two do loops through two datasets but not sure how to put together a similar code as it seems in the web there is no much documentation.
这是我正在处理的代码的示例.可以肯定它有很多错误，但是为了对我的问题提供正确的看法:
Here an example of the code I'm working on. Pretty sure it has many pieces wrong but as in order to offer a proper view of my issue:
data tot_imp;
input contract $ nie $ account_type $ fecha date9. missing_pago;
format fecha date9.;
datalines;
1 xx cc 01SEP2017 5
1 xx cc 04SEP2017 4
;
run;
data pagos;
input contract $ nie $ account_type $ fecha date9. remain_pago;
format fecha date9.;
datalines;
1 xx cc 09SEP2017 3
1 xx cc 12SEP2017 2
;
run;
data tot_imp_el;
set tot_imp nobs=num1;
do k=1 to num1;
partial=0;
do i=1 to num;
set pagos (rename=(contract=con nie=nies account_type=type fecha=fechas)) nobs=num point=i;
if con=contract and nies=nie and fechas>=fecha and remain_pago>0 then do; /* if the pago date is later than the impago and the same pago has not been used for other impagos*/
if missing_impago-remain_pago<=0 then do; /* if the pago pays all the impago */
call symput("cc", partial);
call symput("pago","pago"&cc);
call symput("fecha","fecha_pago"&cc);
partial=partial+1;
&pago=missing_pago;
remain_pago=remain_pago-missing_pago;
missing_pago=0 ;
&fecha=fechas;
drop con nies type fechas remaing_pago;
leave;
end;
else do;
call symput("cc", partial);
call symput("pago","pago"&cc);
call symput("fecha","fecha_pago"&cc);
partial=partial+1;
missing_pago=missing_pago-remain_pago;
&pago=remaing_pago;
&fecha=fechas;
remain_pago=0;
drop con nies type fechas remaing_pago;
end;
end;
end;
run;
推荐答案
这似乎是一种令人迷惑的格式，用于将信息放置在较宽的行中.如果您基本上想要的是每个ID的默认违约和付款分类账，我认为将数据保留在狭窄的列中会更容易.例如，下面交错默认值，它们按ID和日期付款，并创建运行余额:
This seems like a confusing format to put the information in wide rows. If what you want is basically a running ledger of defaults and payments for each ID, I think it would be easier to keep the data in narrow columns. For example, below interleaves the defaults and they payments (by ID and Date), and creates a running Balance:
data default;
input ID amount date ddmmyy8.;
format date date9.;
cards;
1 5 01012015
1 4 10012015
;
run;
data payment;
input ID amount date ddmmyy8.;
format date date9.;
cards;
1 3 15122015 3
1 6 18012016 6
;
data want;
set default (in=d)
payment (in=p)
;
by id date;
if first.id then balance=0;
if d then do;
type='Default';
balance+-amount;
end;
if p then do;
type='Payment';
balance+amount;
end;
run;
proc print data=want;
run;
返回:
Obs ID amount date balance type
1 1 5 01JAN2015 -5 Default
2 1 4 10JAN2015 -9 Default
3 1 3 15DEC2015 -6 Payment
4 1 6 18JAN2016 0 Payment
这篇关于两个数据集之间的SAS迭代循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
