@mkeintz wrote:
Oops sorry. I see you mean 4 trans WITHIN two days, even if only one of those days is present:
BTW, notice the "trick" here is to update the LAG/DIF queues only at the end of each day's set of transactions. Also, even though the "lag(_ntrans)" is inside an IFN function, the lag queue is updated regardless of whether the ifn test is true. Which is what you want - the lag queue is always updated even if its results are not returned by the IFN function.
data want;
do _ntrans=1 by 1 until(last.transactiondate);
set have;
by id transactiondate;
end;
total_count = sum(_ntrans,ifn(dif(transactiondate)=1 and dif(id)=0,lag(_ntrans),0));
if total_count>=4;
run;
This is making a bit more sense now and I think we're almost there with it.
heres a sample of the output I have:-
ID
TransactionDate
total_count
1510110787
18/11/2016
4
1510110787
30/11/2016
4
1510141720
26/11/2016
4
1510390852
21/11/2016
5
1510390852
22/11/2016
7
1510390852
28/11/2016
4
1510472816
20/11/2016
4
I think you touched on this earler in the thread about overlapping days.
For ID 1510390852 there are 5 observations on the 21/11/2016 and 2 on the 22/11/2016.
Instead of the output being 5 and 7 for the 21st and 22nd, as these are within a two day window would just like to see 1 observation with a value of 7.
Is this possible?
Obviously for the first Id in that table as the 18th and the 30th arent within a two day window the 2 obervations with a value of 4 are correct.
Your continued help is appreciated
Stret
... View more