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!
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;
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
;
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;
Thanks, Gergely.. that did it!
Also, Thanks Peter. I was able to adapt yours to a forward looking view.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.