Piggybacking on all the code @Tom proposed here an approach with a cumulative count for days of transactions per ID (=count once per date and id).
The source data must be sorted by date but not by ID within a date - which is how transactional data is often ordered.
data have ;
input date :ddmmyy. id amount;
format date yymmdd10. ;
datalines;
01/03/2019 1 630
01/04/2019 1 90
01/05/2019 1 2
01/05/2019 1 112
01/01/2023 2 20
01/01/2023 2 100
01/01/2023 2 2
01/01/2023 2 10
01/01/2023 2 420
01/02/2023 2 98
;
data want2;
if _n_=1 then do;
/* hash for cumulative count per id and date */
declare hash h_cnt();
h_cnt.definekey('id');
h_cnt.defineData('counter');
h_cnt.definedone();
/* hash to keep track for which id already counted within a date */
declare hash h_id();
h_id.definekey('id','date');
h_id.definedone();
end;
set have;
by date;
/* not a must but keeps memory consumption lower */
if first.date then h_id.clear();
/* if new id set counter=0 else read current count from hash */
if h_cnt.find() ne 0 then counter=0;
/* if counter not increased yet for id and specific date */
if h_id.check() ne 0 then
do;
_rc=h_id.add();
counter+1;
_rc=h_cnt.replace();
end;
drop _rc;
run;
proc print data=want2;
run;
... View more
