topic Re: Creating a binary flag based on the next row in SAS Programming

Creating a binary flag based on the next row

CEdward — Tue, 08 Dec 2020 17:29:21 GMT

Hello,

I have a dataset that looks like the following:

Have

Customer Total_Purchases

1 1

1 2

1 3

Want

Customer Total_Purchases New_Purchase

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?

Re: Creating a binary flag based on the next row

PaigeMiller — Tue, 08 Dec 2020 17:36:10 GMT

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.

Re: Creating a binary flag based on the next row

CEdward — Tue, 08 Dec 2020 17:36:56 GMT

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

Re: Creating a binary flag based on the next row

PaigeMiller — Tue, 08 Dec 2020 17:55:22 GMT

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?

Re: Creating a binary flag based on the next row

Reeza — Tue, 08 Dec 2020 17:57:56 GMT

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;

Re: Creating a binary flag based on the next row

CEdward — Tue, 08 Dec 2020 18:02:36 GMT

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.

Re: Creating a binary flag based on the next row

PaigeMiller — Tue, 08 Dec 2020 18:10:08 GMT

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?

Re: Creating a binary flag based on the next row

Astounding — Tue, 08 Dec 2020 19:15:44 GMT

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;

Re: Creating a binary flag based on the next row

CEdward — Tue, 08 Dec 2020 19:25:42 GMT

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?

Re: Creating a binary flag based on the next row

Reeza — Tue, 08 Dec 2020 19:52:38 GMT

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

Re: Creating a binary flag based on the next row

Ksharp — Wed, 09 Dec 2020 13:47:20 GMT

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;