topic Re: 30 and 90 day suppressions in SAS Programming

30 and 90 day suppressions

stuart_snap — Thu, 30 Jan 2014 18:48:23 GMT

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!

Re: 30 and 90 day suppressions

Peter_C — Fri, 31 Jan 2014 16:04:10 GMT

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

;

Re: 30 and 90 day suppressions

gergely_batho — Tue, 04 Feb 2014 15:26:22 GMT

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;

Re: 30 and 90 day suppressions

stuart_snap — Tue, 04 Feb 2014 17:03:35 GMT

Thanks, Gergely.. that did it!

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