问题描述
我有以下代码来跟踪特定制造商优惠券的发行.每个问题的兑换，过期和剩余字段中都可以包含美元.我有一个ISSUE表，该表提供了所有发行和金额，而REDEMPTIONS表则是对已兑现或过期的金额的说明.目的是通过在每个类别中放置适当的金额来跟踪每期的所有优惠券美元.
I have the below code to track specific manufacturer coupon issuings. Each issue can have dollars in the redeemed, expired and remaining fields. I have an ISSUE table that gives all of the issuings and amounts and the REDEMPTIONS table that is an accounting of amounts redeemed or expired. The objective is to track all coupon dollars for each issue by putting the appropriate amounts in each category.
该代码应该遍历ISSUE表，并将REDEMPTIONS表中的匹配记录绑定到ISSUE表中的每个记录.
This code is supposed to loop through the ISSUE table and tie matching records from the REDEMPTIONS table to each record in the ISSUE table.
/*create sample tables*/
data ISSUE; input
Coupon_NBR $ AMOUNT REDEEMED EXPIRED REMAINING; datalines;
A 500 0 0 500
A 500 0 0 500
B 500 0 0 500
B 500 0 0 500
B 500 0 0 500
B 1250 0 0 1250
B 750 0 0 750
C 500 0 0 500
C 500 0 0 500
C 500 0 0 500
C 500 0 0 500
C 500 0 0 500
run;
data REDEMPTIONS; input
Redemp_coupon_NBR $ TRANS_AMOUNT TYPE $16.; datalines;
A -150 REDEMPTION
A -350 REDEMPTION
A -200 EXPIRATION
B -300 REDEMPTION
B -200 EXPIRATION
B -1000 REDEMPTION
C -1500 REDEMPTION
C -500 EXPIRATION
run;
/*begin looping code*/
data Tracking;
if _n_ = 1 then Link get_redemptions;
set issue;
if (remaining > 0) and (Coupon_NBR = Redemp_coupon_NBR) then do;
if trans_amount = 0 then
link get_redemptions;
if trans_amount + remaining >=0 then do;
remaining = remaining + trans_amount;
if type = 'EXPIRATION' then;
expired = expired - trans_amount;
if type = 'REDEMPTION' then;
redeemed = redeemed - trans_amount;
link get_redemptions;
end;
else do;
remaining = 0;
if type = 'EXPIRATION' then do;
expired = expired - trans_amount;
end;
else do;
redeemed = redeemed - trans_amount;
trans_amount = trans_amount + remaining;
remaining = 0;
end;
end;
end;
else do;
link get_redemptions;
end;
return;
get_redemptions:
set redemptions;
return;
run;
这是我得到的输出:
Coupon_NBR AMOUNT REDEEMED EXPIRED REMAINING redemp_coupon_nbr trans_amount type
A 500 150 150 350 A -350 REDEMPTION
A 500 350 350 150 A -200 EXPIRATION
B 500 0 0 500 B -300 REDEMPTION
B 500 300 300 200 B -200 EXPIRATION
B 500 200 200 300 B -1000 REDEMPTION
B 1250 1000 1000 250 C -1500 REDEMPTION
B 750 0 0 750 C -500 EXPIRATION
在此示例中，正确的输出是:
In this example, the correct output is:
redemp_coupon_nbr AMOUNT REDEEMED EXPIRED REMAINING
A 500 500 0 0
A 500 0 200 300
B 500 300 200 0
B 500 500 0 0
B 500 500 0 0
B 1250 0 0 1250
B 750 0 0 750
C 500 500 0 0
C 500 500 0 0
C 500 500 0 0
C 500 0 500 0
C 500 0 0 500
很明显，我的结果与我想要的结果相去甚远.但是，我主要关心的是，当我希望它跟踪每个优惠券发行时，输出仅七行，这意味着我需要它具有12行(ISSUE表中的每一行一个).我的循环存在某种问题，我认为特别是在 Get Redemptions 定义中.我已经调试了一段时间，没有成功.
Obviously my result is far from where I want it to be. My main concern, however, is that the output only has seven rows, when I want it track every coupon issue, which means I need it to have 12 rows (one for each row in the ISSUE table). There is some kind of a problem with my loop I think specifically in the Get Redemptions definition. I've been debugging for a while without success.
推荐答案
Jarom:
一个强大的解决方案需要一种事务分类帐方法，以便正确处理以下选择:每张优惠券提取多个赎回和跟踪超额，以及在提取额外的赎回之前将超额应用于优惠券.
A robust solution requires a transactional ledgering approach in order to properly deal with the alternatives of fetching multiple redemptions per coupon and tracking overages, and applying overages to a coupon before fetching additional redemptions.
以下示例代码具有大量put语句，因此您可以在日志中观察算法决策点. balance 的目标是在每次交易对帐时(从上或下)接近零，并跟踪超出目标的任何部分.
The following sample code has numerous put statements so you can observe the algorithm decision points in the log. The balance goal is to approach zero (from above or below) at each transaction reconciliation, and track any portions that go beyond the goal.
对于样本，这些变量名的替换是针对您的数据进行的.
For the sample these variable names substitutions were done with respect to your data.
- coupon_nbr-> G
- redemp_coupon_nbr-> XG
- trans_amount-> XAMOUNT
您正确地需要一个 LINK 来提取兑现.
通过添加 BY XG 和 END = ，可以进一步简化按组处理.
You were correct in needing a LINK to fetch the redemptions.
The group wise processing is further facilitated by adding BY XG and END=.
对赎回end =变量进行的测试可防止数据步骤过早停止(如果在读取数据集的最后一条记录后到达随后的无条件集合，则会发生这种情况).
Tests for the redemptions end= variable prevent a premature halting of the data step (which would occur if a subsequent unconditional set is reached after a data sets last record is read).
data reconciliation (keep=G AMOUNT REDEEMED EXPIRED REMAINING EXCESS APP_COUNT _redem_bal _expir_bal fetch_sum)
; * / debug;
set issue;
by G;
REDEEMED = 0;
EXPIRED = 0;
REMAINING = 0;
EXCESS = 0;
if 0 then set redemptions; %* prep pdv;
retain _balance 0;
retain _redem_sum 0;
retain _expir_sum 0;
retain _redem_bal 0;
retain _expir_bal 0;
if first.g then put / '----------- ' G= '-------------';
put '@set ' _N_=;
put 'balance: ' _balance _redem_bal= _expir_bal=;
put 'coupon : ' amount first.g= /;
if first.G then do;
put @3 '@first in group';
_balance = amount;
_redem_sum = 0;
_redem_bal = 0;
_expir_sum = 0;
_expir_bal = 0;
put @3 'balance: ' _balance _redem_bal= _expir_bal= G= XG=;
* spin to first matching redemption or first redemption in a higher by-group;
if (XG ne G) then
do while (not EOT);
link fetch;
if XG >= G then leave;
end;
if (G = XG) then
link apply_redemp;
put @6 'spin: ' G= XG=;
end;
else do; * additional couponage;
put @3 '@next in group';
if (G = XG) then
_balance + amount;
else
_balance = amount;
put @3 'balance: ' _balance _redem_bal= _expir_bal=;
link apply_excess_to_balance;
put @3 'balance: ' _balance _redem_bal= _expir_bal= xg= last.xg=;
if (_balance > 0 and G = XG and not last.XG) then
link fetch_apply;
end;
if (G = XG) then
do while (not EOT and not last.XG and _balance > 0);
link fetch_apply;
end;
redeemed = _redem_sum;
expired = _expir_sum;
remaining = min (_balance, amount);
excess = sum (_redem_bal, _expir_bal, max (0, _balance - amount));
output;
put @4 'output: ' amount= redeemed= expired= remaining= excess= /;
_redem_sum = 0;
_expir_sum = 0;
return;
apply_excess_to_balance:
if (_redem_bal > 0 and _balance > 0) then do;
apply = min ( _balance, _redem_bal );
_redem_sum + apply;
_redem_bal + -apply;
_balance + -apply;
app_count = sum(app_count,1);
put @4 'excess: ' apply= _redem_bal= _redem_sum= _balance= 'reduced amount by excess redemption';
end;
if (_expir_bal > 0 and _balance > 0) then do;
apply = min ( _balance, _expir_bal );
_expir_sum + apply;
_expir_bal + -apply;
_balance + -apply;
app_count = sum(app_count,1);
put @4 'excess: ' apply= _expir_bal= _expir_sum= _balance= 'reduced amount by excess expiration';
end;
return;
fetch:
set redemptions end=EOT;
by XG;
put @5 'fetch: ' xg= xamount= type= first.xg= last.xg= EOT=;
return;
fetch_apply:
link fetch;
if (G = XG) then
link apply_redemp;
return;
apply_redemp:
if type in: ('RED' 'EXP') then do;
apply = min (_balance, -XAMOUNT);
excess = max (0, -XAMOUNT - _balance);
if type =: 'RED' then do;
_redem_sum + apply;
_redem_bal + excess;
end;
else
if type =: 'EXP' then do;
_expir_sum + apply;
_expir_bal + excess;
end;
_balance + -apply;
app_count = sum(app_count,1);
fetch_sum = sum(fetch_sum, -xamount);
put @5 'apply: ' apply= _balance= _redem_sum= _expir_sum= _redem_bal= _expir_bal=;
end;
return;
run;
还有
- 不匹配的兑换
- B组的第一次赎回是很大的一笔，跨越了两个优惠券.
- 没有兑换的优惠券组
棘手的数据
data ISSUE; input
G $ AMOUNT;
A 500
A 500
B 500
B 500
B 500
B 1250
B 750
B2 100
B2 200
C 500
C 500
C 500
C 500
C 500
run;
data REDEMPTIONS; input
XG $ XAMOUNT TYPE $16.; datalines;
! -1000 REDEMPTION
A -150 REDEMPTION
A -350 REDEMPTION
A -200 EXPIRATION
B -1100 REDEMPTION was -300
B -200 EXPIRATION
B -1000 REDEMPTION
C -1500 REDEMPTION
C -500 EXPIRATION
run;
这篇关于带有两个Set语句的SAS中的嵌入式隐式循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！