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;
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.