DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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_idtran_mthclose_mth
1Dec-14Apr-15
1Jan-15Apr-15
1Feb-15Apr-15
1Mar-15Apr-15

 

And the report I am currently getting is:

 Dec-14Jan-15Feb-15Mar-15Apr-15May-15
cust_idclose_mthclose_mthclose_mthclose_mthclose_mthclose_mth
1NoNoNoNo  

 

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


Accepted Solutions
Solution
‎05-31-2016 05:24 AM
Super User
Super User
Posts: 7,432

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

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;

View solution in original post


All Replies
Solution
‎05-31-2016 05:24 AM
Super User
Super User
Posts: 7,432

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

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;
New Contributor
Posts: 2

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

Noted.

Thanks for your help, it is much appreciated.

 

Regards,

 

Angel

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 232 views
  • 0 likes
  • 2 in conversation