Thank you @noling. I used this as a workaround. data have;
input cid $ acctno $ opendate mmddyy10. hicredit memberid $ qtr mmddyy10.;
datalines;
123 x 1/1/2018 2000 ABC 3/1/2018
123 x 1/1/2018 2000 ABC 6/1/2018
123 x 1/1/2018 2000 ABC 9/1/2018
123 x 1/1/2018 2000 ABC 12/1/2018
123 y 1/1/2018 2300 XYZ 3/1/2019
123 y 1/1/2018 2300 XYZ 6/1/2019
123 y 1/1/2018 2300 XYZ 9/1/2019
123 y 1/1/2018 2300 XYZ 12/1/2019
123 z 1/1/2017 5000 XYZ 3/1/2018
123 z 1/1/2017 5000 XYZ 6/1/2018
123 z 1/1/2017 5000 XYZ 9/1/2018
123 z 1/1/2017 5000 XYZ 12/1/2018
123 z 1/1/2017 5000 XYZ 3/1/2019
123 z 1/1/2017 5000 XYZ 6/1/2019
123 z 1/1/2017 5000 XYZ 9/1/2019
123 z 1/1/2017 5000 XYZ 12/1/2019
123 w 1/1/2017 10000 XYZ 3/1/2018
123 w 1/1/2017 10000 XYZ 6/1/2018
123 w 1/1/2017 10000 XYZ 9/1/2018
123 w 1/1/2017 10000 XYZ 12/1/2018
123 w 1/1/2017 10000 XYZ 3/1/2019
123 w 1/1/2017 10000 XYZ 6/1/2019
123 w 1/1/2017 10000 XYZ 9/1/2019
123 w 1/1/2017 10000 XYZ 12/1/2019
;
run;
proc sql;
create table counts as
select distinct cid, opendate, qtr
from have
order by cid, opendate, qtr
;
quit;
data counts;
format opendate mmddyy10. qtr mmddyy10.;
set counts;
count+1;
by cid opendate qtr;
if first.opendate then count=1;
run;
proc sql;
create table want as
select a.*, b.count
from have a left join counts b
on a.cid=b.cid and a.opendate=b.opendate and a.qtr=b.qtr
order by cid, opendate, qtr
;
quit; But you're code is what I was looking for.
... View more
