DATA Step, Macro, Functions and more

Help to Create data

Reply
Frequent Contributor
Posts: 99

Help to Create data

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:

StatusDescription
1offer accepted after contact
2offer accepted without any contact
3Offer still not accepted but contacted
4offer still not accpted but were not contacted

  I appreciate all your help.

Regards,

Amit

Final table:

CustIDOfferCreationContact_DateAcceptDateStatus
day0
Status
day1
Status
day2
Status
day3
Status
day4
102Jul2012 44444
213Aug2012 15Aug201244222
329Jun2012 02Jul201244422
429Jun2012 26Jul201244444
613Aug201215Aug201215Aug201244111
722Aug201225Aug2012 44433
Super User
Posts: 17,784

Re: Help to Create data

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;

Ask a Question
Discussion stats
  • 1 reply
  • 150 views
  • 0 likes
  • 2 in conversation