I all you are doing is getting rolling 12 months counts of unique customers by brand/country, then:
proc sort data=orders out=need nodupkey;
by brand country monthyear custid;
run;
%let minyear=2016; ** One less than actual earliest year **;
%let maxyear=2020;
data counts (keep=brand country monthyear value rolling12);
set need;
by brand country monthyear;
array vals{&minyear:&maxyear,1:12} _temporary_ ;
if first.country then call missing(of vals{*},value,rolling12);
if first.monthyear then value=0;
value+1;
if last.monthyear;
y=year(monthyear);
m=month(monthyear);
vals{y,m}=value;
rolling12+value ;
if vals{y-1,m}>0 then rolling12=rolling12-vals{y-1,m};
run;
This keeps a two dimensional (rows are years, months are columns) array of customer counts. Counting customers within each brand/countery/monthyear is easy. Then, at the last record of each monthyear, update the array with the new value. And update the rolling12 by adding VALUE to it, and subtracting the value from 12 months prior.
Set the macrovar MINYEAR to 1 less than the earliest year in your data. MINYEAR identifies the first row in the array, so it becomes a valid (though unused) row identity when referring to 12 months prior to the first actual monthyear.
Note that while any starting and ending monthyear can be accomodated in this program, it assumes there are no missing internal months for any brand/country combination. However, a few extra lines of code could accommodate that situation.
Click image to register for webinar
