30 and 90 day suppressions

[stuart_snap]

Hi again all.

I am looking for some input on getting suppressions working properly.  Here is 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 attached some sample input data (in yellow) and the desired output (in blue).  Any thoughts?  The suppressions individually are easy... but the combination of the 2 isn't as easy as I had hoped.

Thanks!

[Peter_C]

proc sql ;

create table un_suppressed as

select cus.*

from customer_data as cus

left

join ( select distinct customer as old_cus

        from customer_data

        where purchase > (today() - 30 )

     ) old1

on cus.customer = old1.old_cus

left

join ( select distinct customer old_cus, category as old_cat

        from customer_data

        where purchase > (today() - 90 )

     ) old2

on cus.customer = old2.old_cus

and cus.category = old2.old_cat

where old1.old_cus is null

  and old2.old.cat is null

;

quit ;

*

The idea is to create and join with the additional data streams I have labelled with aliases OLD1 and OLD2.

These identify (OLD1) - customers who purchased within 30 days

and (OLD2) - customers who bought that category within 90 days

If either of those joins take effect their OLD1 customer or OLD2 cat will not be null so should be suppressed. SO both of those must be NULL

;

[gergely_batho · Accepted solution]

proc sort data=have;

by Customer Purchase;

run;

/*

Storing the last send date in lastSendDate.

Storing the last send date for each Category in a hash table for the "active" customer.

For every purchase check the last send date and the last date for that category.

When we need to send, update lastSendDate and the hash table.

*/

data want(keep=Customer Category Purchase);

if _n_=1 then do;

     dcl hash lastSend();

     lastSend.defineKey('Category');

     lastSend.defineData('lastSendDateCat');

     lastSend.defineDone();

end;

set have;

by Customer Purchase;

retain lastSendDate;

if first.Customer then do;

     lastSend.clear();

     lastSendDate='01jan1900'd;/*very old */

end;

lastSendDateCat='01jan1900'd;/*very old*/

if lastSendDate+30<=Purchase and (lastSend.find(key:Category) or lastSendDateCat+90<=Purchase) then do;

     output;

     lastSendDate=Purchase;

     rc=lastSend.add(key:Category, data:Purchase);

     rc=lastSend.replace(key:Category, data:Purchase);

end;

run;

[stuart_snap]

Thanks, Gergely.. that did it!

Also, Thanks Peter.  I was able to adapt yours to a forward looking view.