topic Re: How do I create a rolling 12 months column? in SAS Programming

How do I create a rolling 12 months column?

SURIM — Mon, 09 Nov 2020 08:25:20 GMT

Hi, I need help. Have look at several other posts similar to my problem but can't get it to work.

I have several companies and countries in the same code, but for simplicity I have picked Company A and B here, and 2 countries.

I have aggregated unique customers on company, country and monthyear with the result below.

Now I need a rolling 12 number as shown for Company A GB (R_12). (I have done below numbers manually). How do I sum active customers for each row, 12 months back?

Company	Country	monthyear	active_customers	R_12
CompA	GB	nov-19	21494	240896
CompA	GB	dec-19	39757	246317
CompA	GB	jan-20	18798	241754
CompA	GB	feb-20	13269	244985
CompA	GB	mar-20	29414	254806
CompA	GB	apr-20	24611	265860
CompA	GB	maj-20	34963	283267
CompA	GB	jun-20	46990	302971
CompA	GB	jul-20	36690	317735
CompA	GB	aug-20	30943	337245
CompA	GB	sep-20	37183	353682
CompA	GB	okt-20	34910	369022
CompA	US	nov-19	12340
CompA	US	dec-19	18859
CompA	US	jan-20	15897
CompA	US	feb-20	9258
CompA	US	mar-20	16984
CompA	US	apr-20	15842
CompA	US	maj-20	21011
CompA	US	jun-20	33483
CompA	US	jul-20	25626
CompA	US	aug-20	16271
CompA	US	sep-20	22536
CompA	US	okt-20	26168	234275
CompB	GB	nov-19	46955
CompB	GB	dec-19	47438
CompB	GB	jan-20	33135
CompB	GB	feb-20	31908
CompB	GB	mar-20	33641
CompB	GB	apr-20	34317
CompB	GB	maj-20	54933
CompB	GB	jun-20	54046
CompB	GB	jul-20	45186
CompB	GB	aug-20	39741
CompB	GB	sep-20	49857
CompB	GB	okt-20	51969	523126
CompB	US	nov-19	27798
CompB	US	dec-19	22579
CompB	US	jan-20	22078
CompB	US	feb-20	15865
CompB	US	mar-20	20571
CompB	US	apr-20	12532
CompB	US	maj-20	22796
CompB	US	jun-20	28999
CompB	US	jul-20	26800
CompB	US	aug-20	20284
CompB	US	sep-20	29318
CompB	US	okt-20	29402	279022

Re: How do I create a rolling 12 months column?

Kurt_Bremser — Mon, 09 Nov 2020 08:46:08 GMT

From where do you get the starting value? A rolling window for CompA and GB would start with 21494.

Re: How do I create a rolling 12 months column?

SURIM — Mon, 09 Nov 2020 08:53:39 GMT

On those countries I have data 2 years back, but thought it would be to long to incorporate. But let me know if that would be useful to add!

Re: How do I create a rolling 12 months column?

Kurt_Bremser — Mon, 09 Nov 2020 09:14:39 GMT

OK. Assuming your dataset is correctly sorted by company, country and monthyear:

data want;
set have;
by company country;
array win{0:11} _temporary_;
if first.country
then do;
  call missing(of win{*});
  count = 0;
end;
else count + 1;
win{mod(count,12)} = active_customers;
r12 = sum(of win{*});
drop count;
run;

Untested.

Re: How do I create a rolling 12 months column?

SURIM — Mon, 09 Nov 2020 09:37:53 GMT

This works perfectly! Thank you Kurt, you have no idea how happy and relieved this makes me.
THANKS!

Re: How do I create a rolling 12 months column?

Kurt_Bremser — Mon, 09 Nov 2020 09:52:57 GMT

Fine!

Feel free to ask if any of the elements used are not completely clear to you.