Hi @havmaage
You datalines don't contain such cases...
Assuming we have the following ones:
- Ann has changes in amount every days.
- Simon has changes in amount every 5 or 7 days <=> has no change in amount for at least 4 consecutive days.
Is that correct?
Do we assume there is always one record per day for each name?
Thank you for the clarifications.
If it is correct, you can try this:
data have;
infile datalines dlm=",";
input name $ date:YYMMDD10. amount;
format date YYMMDD10.;
datalines;
ann, 2020-05-11, 1000
ann, 2020-05-12, 1200
ann, 2020-05-13, 1300
pete, 2020-05-11, 1200
pete, 2020-05-12, 1200
pete, 2020-05-13, 1200
simon, 2020-05-11, 200
simon, 2020-05-12, 1200
simon, 2020-05-13, 1200
simon, 2020-05-14, 1200
simon, 2020-05-15, 1200
simon, 2020-05-16, 200
simon, 2020-05-17, 400
simon, 2020-05-18, 1200
;
run;
/* Count consecutive amounts */
data have2;
set have;
by name date amount;
_lag = lag(amount);
if first.name then consec = 0;
if amount ne _lag then consec+1;
drop _lag;
run;
proc freq data=have2 noprint;
table name*consec / out=have_freq (drop=consec percent);
run;
/*** Names where the amount changes
every day
(Assuming there is one record per day for each name) */
proc sql;
select name
from have2
group by name
having count(distinct amount) = count(distinct date);
quit;
/*** Names where the amount
changes every 5 or 7 days
<=> does not change for at least 4 days
(Assuming there is one record per day for each name) */
proc sql;
select name
from have_freq
group by name
having max(count) >=4;
quit;
... View more
