About SURIM

SURIM · ‎03-26-2021

To circumvent the problem, do the following: In a new Firefox browser tab, enter about:config. Search for the dom.w3c_pointer_events.enabled preference and set its value to false. Restart Firefox.

SURIM · ‎11-26-2020

I kept trying with the suggested code and other codes and manage to get as close as I will get. data need; set have; by brand country; array win{0:11} _temporary_; if first.monthyear then do; call missing(of win{*}); count = 0; end; if first.country_ then do; call missing(of win{*}); count = 0; end; else count + 1; win{mod(count,12)} = value; r12 = sum(of win{*}); drop count; run;

SURIM · ‎11-19-2020

So, when I run this code the "need" table looks good and reasonable. Brand Country monthyear value BrandA SE 01-jan-19 2498 BrandA SE 01-feb-19 2590 BrandA SE 01-mar-19 3315 BrandA SE 01-apr-19 3787 BrandA SE 01-maj-19 4523 BrandA SE 01-jun-19 4588 BrandA SE 01-jul-19 4222 BrandA SE 01-aug-19 4897 BrandA SE 01-sep-19 4834 BrandA SE 01-okt-19 5231 BrandA SE 01-nov-19 4595 BrandA SE 01-dec-19 5826 BrandA SE 01-jan-20 5180 BrandA SE 01-feb-20 5812 BrandA SE 01-mar-20 6940 BrandA SE 01-apr-20 9144 BrandA SE 01-maj-20 11496 BrandA SE 01-jun-20 14483 BrandA SE 01-jul-20 11671 BrandA SE 01-aug-20 16985 BrandA SE 01-sep-20 19664 BrandA SE 01-okt-20 17903 But something gets wrong when it comes to the "counts" table Brand Country monthyear value rolling12 BrandA SE 01-dec-17 1 1 BrandA SE 01-jan-18 1 2 BrandA SE 01-feb-18 1 3 BrandA SE 01-mar-18 1 4 BrandA SE 01-apr-18 1 5 BrandA SE 01-maj-18 1 6 BrandA SE 01-jun-18 1 7 BrandA SE 01-jul-18 1 8 BrandA SE 01-aug-18 1 9 BrandA SE 01-sep-18 1 10 BrandA SE 01-okt-18 1 11 BrandA SE 01-nov-18 1 12 BrandA SE 01-dec-18 1 12 BrandA SE 01-jan-19 1 12 BrandA SE 01-feb-19 1 12 BrandA SE 01-mar-19 1 12 BrandA SE 01-apr-19 1 12 BrandA SE 01-maj-19 1 12 BrandA SE 01-jun-19 1 12 BrandA SE 01-jul-19 1 12 BrandA SE 01-aug-19 1 12 BrandA SE 01-sep-19 1 12 BrandA SE 01-okt-19 1 12 BrandA SE 01-nov-19 1 12 BrandA SE 01-dec-19 1 12 BrandA SE 01-jan-20 1 12 BrandA SE 01-feb-20 1 12 BrandA SE 01-mar-20 1 12 BrandA SE 01-apr-20 1 12 BrandA SE 01-maj-20 1 12 BrandA SE 01-jun-20 1 12 BrandA SE 01-jul-20 1 12 BrandA SE 01-aug-20 1 12 BrandA SE 01-sep-20 1 12 BrandA SE 01-okt-20 1 12

SURIM · ‎11-19-2020

Yes orders and customers are merged to get the email variable, since that is only in the customers table, but I need all of the orders to be able to count a 12month period every month. I honestly thought it would be easier to figure out a solution, but have tried writing the code in 50 different ways… Thanks for helping out.

SURIM · ‎11-19-2020

The value is the count of unique customers by brand, country, monthyear. Sorry this wasn’t clear. Is there something else I can attach to make it better?

SURIM · ‎11-18-2020

I have attached sample data.

SURIM · ‎11-18-2020

Hi, I've look at at least 12 similar posts with out getting it right. I hope someone can solve this one! The below code is a preparation for a rolling 12 months total active customer count, that keeps rolling over they years. What code should I use to continue and get: * IMPORTANT: For each 12 months period, the same cust_id can only be counted once. * I want as the table below (but with the above statement in mind) data orders; set order_info; keep brand cust_id country monthyear value; monthyear = intnx('month',t_dat,0,"BEGINNING"); format monthyear date9.; value=1; run; proc sort data=orders nodupkey; by brand country cust_id monthyear; run; data customers; set customer_info; keep brand cust_id email country; run; proc sort data=customers nodupkey; by brand email cust_id country; run; data merge_orders_customers; merge orders (in=a) customers (in=b); by brand cust_id country; if a and b; run; proc sort data=merge_orders_customers; by brand email country descending monthyear; run; proc sort data=merge_orders_customers nodupkey; by brand email country; run; proc means data=merge_orders_customers noprint; output out = total sum=; by brand country monthyear; run; Brand Country monthyear value rolling12 BrandA GB 01-dec-17 4337 4337 BrandA GB 01-jan-18 3995 8332 BrandA GB 01-feb-18 1953 10285 BrandA GB 01-mar-18 2961 13246 BrandA GB 01-apr-18 2728 15974 BrandA GB 01-maj-18 3586 19560 BrandA GB 01-jun-18 5167 24727 BrandA GB 01-jul-18 4676 29403 BrandA GB 01-aug-18 3610 33013 BrandA GB 01-sep-18 4545 37558 BrandA GB 01-okt-18 6114 43672 BrandA GB 01-nov-18 6403 50075 BrandA GB 01-dec-18 10531 56269 BrandA GB 01-jan-19 7655 59929 BrandA GB 01-feb-19 5435 63411 BrandA GB 01-mar-19 8170 68620 BrandA GB 01-apr-19 7786 73678 BrandA GB 01-maj-19 8205 78297 BrandA GB 01-jun-19 12129 85259 BrandA GB 01-jul-19 11270 91853 BrandA GB 01-aug-19 8494 96737 BrandA GB 01-sep-19 10344 102536 BrandA GB 01-okt-19 21546 117968 BrandA GB 01-nov-19 14789 126354 BrandA GB 01-dec-19 26496 142319 BrandA GB 01-jan-20 20308 154972 BrandA GB 01-feb-20 14091 163628 BrandA GB 01-mar-20 22798 178256 BrandA GB 01-apr-20 33769 204239 BrandA GB 01-maj-20 34623 230657 BrandA GB 01-jun-20 41845 260373 BrandA GB 01-jul-20 35616 284719 BrandA GB 01-aug-20 30437 306662 BrandA GB 01-sep-20 52758 349076 BrandA GB 01-okt-20 72768 400298

SURIM · ‎11-09-2020

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

SURIM · ‎11-09-2020

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!

SURIM · ‎11-09-2020

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

SURIM · ‎12-03-2019

Marvelous! Thank you for helping out. This almost worked, I just changed the final merge to: want1 and tableA.

SURIM · ‎12-03-2019

Thanks for your post, I have updated the logic. Sorry about that I almost always use merge as a left join. No need to shame us SAS newbies. I am just trying to learn.

SURIM · ‎12-03-2019

Sorry, I have updated with new logic. I think this might work if only changed a little bit.

SURIM · ‎12-03-2019

I tried this, but it deleted all of the id:s that were in both tables. That is why I think this is tricky...

SURIM · ‎12-03-2019

Hello! I've updated with expected outcome. Thanks!

Online Status	Offline
Date Last Visited	‎11-18-2021 05:44 AM

Re: SAS drag-and-drop not working

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Rolling 12 months with distinct customer count

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

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

How do I create a rolling 12 months column?

Re: Rolling 12 months with distinct customer count

Re: Put the first date as cohort dates for all id:s

Re: Numbering the nth occurrence

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

Re: SAS drag-and-drop not working

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Re: Rolling 12 months with distinct customer count

Rolling 12 months with distinct customer count

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

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

How do I create a rolling 12 months column?

Re: Merge tables, but delete duplicates

Re: Merge tables, but delete duplicates

Re: Merge tables, but delete duplicates

Re: Merge tables, but delete duplicates

Re: Merge tables, but delete duplicates

SAS Analytics Explorers