Hi Team, my database has millions of register with different IDs, i will show just an example with one ID. data temp;
infile DATALINES dsd missover;
input DATE:DDMMYY10. value_payment ID;
FORMAT DATE DDMMYY10.;
CARDS;
16/11/2020,9000,1
02/12/2020,9000,1
03/12/2020,8000,1
03/12/2020,6000,1
03/12/2020,5000,1
03/12/2020,1000,1
07/12/2020,2000,1
29/12/2020,3000,1
29/12/2020,4000,1
29/12/2020,9999,1
29/12/2020,2000,1
26/01/2021,5000,1
26/01/2021,1111,1
26/01/2021,1000,1
01/02/2021,2000,1
01/02/2021,3000,1
01/02/2021,1000,1
02/02/2021,5000,1
02/02/2021,7000,1
02/02/2021,8000,1
02/02/2021,9000,1
08/02/2021,10000,1
08/02/2021,1000,1
08/02/2021,4444,1
01/06/2020,3000,1
;
run;
PROC SORT DATA=temp;
BY DATE ID;
RUN;
data test;
set temp;
FORMAT tempdate ddmmyy10.;
by ID DATE ;
tempdate=lag(DATE);
if first.ID then tempdate=DATE;
retain cflag;
if first.ID then cflag=0;
if DATE < INTNX('DAY',tempdate,5) then cflag=cflag+1;
run; My difficulty is to count only if the ID is the same and the first date cannot be longer than 05 days compared to the second date, because i need to identify 4 or more payments within 5 or more days. Result of my code: the result that i need: DATE value_payment ID tempdate cflag
01/06/2020 3000 1 01/06/2020 1
16/11/2020 9000 1 01/06/2020 1
02/12/2020 9000 1 16/11/2020 1
03/12/2020 8000 1 02/12/2020 2
03/12/2020 6000 1 03/12/2020 3
03/12/2020 5000 1 03/12/2020 4
03/12/2020 1000 1 03/12/2020 5
07/12/2020 2000 1 03/12/2020 6
29/12/2020 3000 1 07/12/2020 1
29/12/2020 4000 1 29/12/2020 2
29/12/2020 9999 1 29/12/2020 3
29/12/2020 2000 1 29/12/2020 4
26/01/2021 5000 1 29/12/2020 1
26/01/2021 1111 1 26/01/2021 2
26/01/2021 1000 1 26/01/2021 3
01/02/2021 2000 1 26/01/2021 1
01/02/2021 3000 1 01/02/2021 2
01/02/2021 1000 1 01/02/2021 3
02/02/2021 5000 1 01/02/2021 4
02/02/2021 7000 1 02/02/2021 5
02/02/2021 8000 1 02/02/2021 6
02/02/2021 9000 1 02/02/2021 7
08/02/2021 10000 1 02/02/2021 1
08/02/2021 1000 1 08/02/2021 2
08/02/2021 4444 1 08/02/2021 3 Tks!!!!!
... View more
