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


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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
LinusH
Tourmaline | Level 20

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

Data never sleeps
stuart_snap
Fluorite | Level 6

Working up some fake stuff now;)

stuart_snap
Fluorite | Level 6


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
Super User

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
Super User

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
Fluorite | Level 6


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
Super User

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
Fluorite | Level 6

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!

Reeza
Super User

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

stuart_snap
Fluorite | Level 6

14.  That could change, but static for now.

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 11 replies
  • 1565 views
  • 6 likes
  • 3 in conversation