Creating a binary flag based on the next row

[CEdward]

Hello,

I have a dataset that looks like the following:

Have

Customer Total_Purchases

1 1 

1 1 

1 1 

1 2 

1 2 

1 3 

Want

Customer Total_Purchases New_Purchase

1 1 0

1 1 0 

1 1 1

1 2 0

1 2 1 

1 3 0

Essentially what I want to do is create a flag (New_Purchase) that indicates 1 if and only if the next row increments up by 1 and 0 otherwise. So in this case the New_Purchase variable will equal 1 as highlighted in the want dataset as an example. 

Is there a way to do this?

[PaigeMiller]

You could use a "look-ahead". However, I note you haven't really told us what happens if the data switches to a different customer, and so you should really keep in mind that you need to provide us realistic examples.

I was going to produce code for a "look-ahead", but it would only work on your data which is for one customer, and so it would be relatively useless code. I think I'll wait until you address the above issue.

[CEdward]

Paige,
The same pattern would repeat for all customers in the data.

[PaigeMiller]

I'm afraid I don't understand what you want to do when you get a new customer. If total_purchases on the first row of customer 2 increment by 1 from the last row of customer 1, what do you want to do there?

[CEdward]

I see.
So every customer starts at a single purchase. The data would never look like this:
Have

Customer Total_Purchases
1 1
1 1
1 1
1 2
1 2
1 3
2 4
2 4
2 4
2 5

Is this what you are asking? In this example, you are right, the last row of customer 1 would get a 1 in the New_Purchase flag. This isn't preferred.

[PaigeMiller]

Okay, so now we know what the data for two customers will not look like. Can you show us a realistic example of data for two customers and which flags you want where?

[CEdward]

Hope this clears it up:

Have

Customer Total_Purchases
1 1
1 1
1 1
1 2
1 2
1 3
2 1
2 1
2 1
2 2
2 2



Customer Total_Purchases New_Purchase
1 1 0
1 1 0
1 1 1
1 2 0
1 2 1
1 3 0
2 1 0
2 1 0
2 1 1
2 2 0
2 2 0

Does that help?

[Reeza]

@ballardw solution works, But it just flags the last record for each purchase, except on the last record for the person.

BY groups processing makes this fairly trivial and likely that type of logic is useful for your next steps.

[Reeza]

What happens in this case?

If 8 still gets a one the solution below should work. 

Have
Customer Total_Purchases
1 1 
1 1 
1 1 
1 2 
1 2 
1 8 

 

For your example data this works but won't scale and is likely wrong.

data want;
set have;
by customer total_purchases;
if last.total_purchases then flag=1;
else flag=0;
run;

[Astounding]

Here's how I see it.  Assuming your data is in the proper order as you have presented it:

data want;
   set have;
   by customer total_purchases;
   new_purchase = last.total_purchases and not last.customer;
run;

[Ksharp]

data have;
input Customer Total_Purchases ;
cards;
1 1
1 1
1 1
1 2
1 2
1 3
2 1
2 1
2 1
2 2
2 2
;
data want;
 merge have have(keep=Customer Total_Purchases
          rename=(Customer=_Customer Total_Purchases=_Total_Purchases) firstobs=2);
 if  Customer=_Customer  and  Total_Purchases+1=_Total_Purchases  then  New_Purchase=1;
  else  New_Purchase=0;
drop _: ;
run;