DATA Step, Macro, Functions and more

running day count that starts over after 30 days?

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

running day count that starts over after 30 days?


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!


Accepted Solutions
Solution
‎01-30-2014 11:05 AM
Super User
Posts: 19,787

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

Posted in reply to stuart_snap

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;

View solution in original post


All Replies
Super User
Posts: 5,426

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

Posted in reply to stuart_snap

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

Data never sleeps
Contributor
Posts: 31

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

Working up some fake stuff nowSmiley Wink

Contributor
Posts: 31

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


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!

Super User
Posts: 19,787

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

Posted in reply to stuart_snap

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.

Super User
Posts: 19,787

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

Posted in reply to stuart_snap

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;

Contributor
Posts: 31

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


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

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!

Solution
‎01-30-2014 11:05 AM
Super User
Posts: 19,787

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

Posted in reply to stuart_snap

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;

Contributor
Posts: 31

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

Posted in reply to stuart_snap

one step ahead of you on that one!  Smiley Happy  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!

Super User
Posts: 19,787

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

Posted in reply to stuart_snap

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

Contributor
Posts: 31

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

14.  That could change, but static for now.

Super User
Posts: 19,787

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

Posted in reply to stuart_snap

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 610 views
  • 6 likes
  • 3 in conversation