How to create a flag in a data set base on a condition

[angel_aksentiev]

Hello,

I've encountered a situation, which puzzles me a bit.

I have a spending data where I've summarised the spending bevaviour of a number of customers per month for the period of 12 months. I need to flag whether customers have closed their accounts and flag the month that they've done so. The issue comes when there are people who's closure month is after the month they've lastly transacted in. For example, I have a customer, who's last transaction is in March2015 but they've closed their account in April2015. However, I need to bring the transaction month as an across variable in proc report and I don't want to flag the customer as closed before they'd done so. Since there are no observations for this customer post March 2015 I can't figure out a way to flag them with a condition, so that I wouldn't have to go and manually search for every person who's situation is the same.

My data looks somthing like the following:

cust_id	tran_mth	close_mth
1	Dec-14	Apr-15
1	Jan-15	Apr-15
1	Feb-15	Apr-15
1	Mar-15	Apr-15

And the report I am currently getting is:

	Dec-14	Jan-15	Feb-15	Mar-15	Apr-15	May-15
cust_id	close_mth	close_mth	close_mth	close_mth	close_mth	close_mth
1	No	No	No	No

My question is, is there a way to add a dummy row in the data set that contains the closure month and forced transaction month that equals to the closure month so that I'll be able to flag the customer as closed in April 2015?

Thanks in advance

Angel Aksentiev

[RW9 · Accepted solution]

You can just do a do loop on last.id (assuming sorted) where the date doesn't equal the close date (note it is good to post your test data in the form of a datastep to avoid us having to do data entry):

data have;
  informat tran_mth close_mth date9.;
  input cust_id	tran_mth close_mth;
  format tran_mth close_mth date9.;
datalines;
1	01Dec2014	01Apr2015
1	01Jan2015	01Apr2015
1	01Feb2015	01Apr2015
1	01Mar2015	01Apr2015
;
run;

data want (drop=i);
  set have;
  by cust_id;
  if last.cust_id and close_mth ne tran_mth then do;
    output;
    do i=1 to intck('month',tran_mth,close_mth);
      tran_mth=intnx('month',tran_mth,i);
      output;
    end;
  end;
  else output;
run;

[angel_aksentiev]

Noted.

Thanks for your help, it is much appreciated.

Regards,

Angel