help with data step. find consecutive days that a condition is met for the card.

[P_S_]

Hi,

I have a dataset below that shows transaction date and amount for the card_num. I need to find how many consecutive days did the card transact with amount of >= 150.

I need to create a variable(cons_days) that will track the 1st day the condition is met for the card  and increment the counter if the condition is met for the card in the consecutive days. I have included a picture of the desired output.

I appreciate your help.

Thank you,

data transaction;
input @1 card_num 2.
   @4 amount 3.
   @8 tran_date MMDDYY10. ;
format tran_date date9.;
datalines;
11 100 01/05/2015
11 150 01/06/2015
11 200 01/07/2015
11 250 01/08/2015
11 250 01/20/2015
12 150 01/15/2015
12 300 02/25/2015
13 100 01/08/2015
14 400 01/02/2015
15 100 01/02/2015
15 150 01/03/2015
15 150 01/04/2015
15 150 02/01/2015
15 250 02/02/2015
;
run;

DESIRED OUTPUT:

[ballardw]

First thing, your example data step does not run.

You do not mention what should happen if the amount drops below the 150 value after being greater.

The following assigns 0 instead of missing for consecutive days and would reset within to 0 within a card_num if the value goes below 150.

data want;
   set transaction;
   retain cons_days;
   by card_num;
   if first.card_num then cons_days =0;
   if amount ge 150 then cons_days+1;
run;

Retain tells SAS to keep the value of the variable across iterations of the data step, so is often encountered for counting across records like this.

When BY variables are specified SAS creates automatic variables First. and Last. that indicate whether the current value of that variable is the first, or last, of that level and is a 1/0 or true/false value. So you can use that to reset things when the value changes.

[P_S_]

Hi ballardw;

I have fixed the data step code. it should run now.

Thank you for the idea. I ran your code and it seems to me that the counter for cons_days is simply incrementing for the card if the tran_amt is >= 150.
I need to increment it only if the condition is met (tran_amt >= 150 ) and it also need to be consecutive day. For eg. IF you look at row 19 of my "desired output" picture, the cons_day is reset to 1 because the date in row 19 is jan 20, 2015 and the previous date for this card is Jan 08, 2015. So it is not continuous (consecutive) day. So we would reset it to 1 rather than increment it to a 4. Hope this makes sense.

[ballardw · Accepted solution]

data want;
   set transaction;
   retain cons_days;
   by card_num;
   daydif = dif (tran_date);
   if first.card_num then cons_days =0;
   if amount ge 150 and daydif=1 then cons_days+1;
   else if amount ge 150 then cons_days=1;
   drop daydif;
run;

The dif function returns the the current value - the value from the previous time the statement executes. (Most uses mean do not use the DIF or LAG function in conditional statements).

[P_S_]

This works. The dif function is pretty neat. Thank you ballardw. I appreciate your help.