Hi all,
I have 3 dates for each customer. An offer Creation date, A contact date ( not all are contacted, and an offer accept date ( customer can accept without being contacted. I want help to create following data shown in table.
The "Status Day 0" is the offer creation date. For each day i need to classify the customers to one of the following statuses:
Status | Description |
1 | offer accepted after contact |
2 | offer accepted without any contact |
3 | Offer still not accepted but contacted |
4 | offer still not accpted but were not contacted |
I appreciate all your help.
Regards,
Amit
Final table:
CustID | OfferCreation | Contact_Date | AcceptDate | Status day0 | Status day1 | Status day2 | Status day3 | Status day4 |
1 | 02Jul2012 | 4 | 4 | 4 | 4 | 4 | ||
2 | 13Aug2012 | 15Aug2012 | 4 | 4 | 2 | 2 | 2 | |
3 | 29Jun2012 | 02Jul2012 | 4 | 4 | 4 | 2 | 2 | |
4 | 29Jun2012 | 26Jul2012 | 4 | 4 | 4 | 4 | 4 | |
6 | 13Aug2012 | 15Aug2012 | 15Aug2012 | 4 | 4 | 1 | 1 | 1 |
7 | 22Aug2012 | 25Aug2012 | 4 | 4 | 4 | 3 | 3 |
I would create a long version of my dataset first (something like below, which is untested). And then transpose it, this seems easier because you don't have to know how many days you have ahead of time. Then you can fill in the missing days with the last observation using arrays.
I also haven't worked out the if conditions, but that seems relatively straightforward.
data want;
set have;
end_loop=max(offer_creation, contact_date, acceptdate);
day=0;
do date_ordered=offercreation to end_loop;
*Create if conditions here to assign status to each day;
if date_ordered=offercreation then status=4;
else if ...
;
output;
day+1;
end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.