BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SURIM
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
SURIM
Obsidian | Level 7
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!
Kurt_Bremser
Super User

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.

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1337 views
  • 4 likes
  • 2 in conversation