topic Re: running day count that starts over after 30 days? in SAS Programming

running day count that starts over after 30 days?

stuart_snap — Wed, 29 Jan 2014 21:38:22 GMT

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!

Re: running day count that starts over after 30 days?

LinusH — Wed, 29 Jan 2014 22:03:21 GMT

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

Re: running day count that starts over after 30 days?

stuart_snap — Wed, 29 Jan 2014 22:05:03 GMT

Working up some fake stuff now;)

Re: running day count that starts over after 30 days?

stuart_snap — Wed, 29 Jan 2014 22:33:10 GMT

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!

Re: running day count that starts over after 30 days?

Reeza — Wed, 29 Jan 2014 22:46:07 GMT

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.

Re: running day count that starts over after 30 days?

Reeza — Wed, 29 Jan 2014 22:55:05 GMT

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;

Re: running day count that starts over after 30 days?

stuart_snap — Thu, 30 Jan 2014 16:02:03 GMT

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!

Re: running day count that starts over after 30 days?

Reeza — Thu, 30 Jan 2014 16:05:20 GMT

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;

Re: running day count that starts over after 30 days?

stuart_snap — Thu, 30 Jan 2014 16:16:43 GMT

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!

Re: running day count that starts over after 30 days?

Reeza — Thu, 30 Jan 2014 16:44:48 GMT

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

Re: running day count that starts over after 30 days?

stuart_snap — Thu, 30 Jan 2014 16:49:51 GMT

14. That could change, but static for now.

Re: running day count that starts over after 30 days?

Reeza — Thu, 30 Jan 2014 18:37:34 GMT

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.