running day count that starts over after 30 days?

[stuart_snap]

Hi all

I am working on updating our contact management system.  I wrote a program that does what I am about to ask, but it has a ton of steps and is getting hard to maintain.  I was hoping someone in SAS Land had something that does something similar (a macro?) to this that I could work off of.  Any help is GREATLY appreciated!

I basically need a count of days from a customer's first order within a category, that then starts over after 30 days.  Example... Customer A buys a shirt on Jan 3 (day_count=1), Jan 7 (day_count=5), Feb 1 (day_count=30), and Feb 16 (would be day_count=45, but I want it to start over at day_count=1 since it's after day 30), and so on  The count is easy with first., but how do I get it start over when day_count>30?

An additional augmentation to the process would be to get a day count for that customer between category purchases  (he buys a shirt on Jan 1 (cat_day_count=1) and buys pants on Feb 16 cat_day_count=45)) that starts over after 90 days... but if I can get the first part working, I can work this out.

Thanks again!

[LinusH]

By attaching some sample input and desired output data I bet you'll get us going...!

[stuart_snap]

Working up some fake stuff now;)

[stuart_snap]

Attached is some generic data. The inputs are in yellow, the desired output vars are in green, and there are some notes in blue.

Thanks a billion for any help!

[Reeza]

Can you post some sample data for what you're looking for?

I can't get the picture here.

For doing date calcs look into intnx/intck functions, which can also take custom intervals.

[Reeza]

data want;

set have;

BY customer category;

retain first_day;

if first.category then do;

     first_day=purchase;

end;

if purchase-first_day+1>30 then first_day=purchase;

running_count1=purchase-first_day+1;

run;

[stuart_snap]

Thank you!!!!!!!!!! That answered 100% of what I asked.  However, after thinking about it, only answered about 80% of what I need:)

Hopefully, I can get some more help!  Let me state the ultimate goal... When a customer orders, I want to mail them a survey about the product.  I don't want to mail that customer again about that product for 90 days, or about any product for 30 days.  So, after they are surveyed once, they are completely suppressed for all surveys for 30 days, and suppressed for surveys on that product for 90 days.  For each customer, I want to use historical data to show when each client would have been mailed.  I have reworked the sample data to show what I have and I need.  T

Thanks again!  I feel like an idiot, but for some reason I can't lock this logic in!

[Reeza · Accepted solution]

It sounds like you only want records where the count is 1. Try adding the line below.

data want;

set have;

BY customer category;

retain first_day;

if first.category then do;

     first_day=purchase;

end;

if purchase-first_day+1>30 then first_day=purchase;

running_count1=purchase-first_day+1;

if running_count1=1 then output;

run;

[stuart_snap]

one step ahead of you on that one!    That gives me all the eligible mail days using the 30 day rule.  I now have to take the 90 day rule into account.  I tried using the same logic for 90 days and then just merging the 2 eligible universes.  But, that gets some false positives.

I'll keep chugging along.  Thanks again for your help!

[Reeza]

How many products are you likely to have? Is it fixed or dynamic?

[stuart_snap]

14.  That could change, but static for now.

[Reeza]

You may want to mark this question and answered and re-post your new question.

I think the fundamentals behind the new question is actually different and this solution won't work for it as well. You'll get more responses this way.