SAS adding an incremental counter based on a condition

Question

I have the following dataset in SAS

Id   date        order   amount
101  5/20/2020     1       25
101  5/20/2020     2       25
101  5/20/2020     3        0
101  5/21/2020     1       25
101  5/21/2020     2       25

I need to add a counter only amount=25 based on ‘Id’, ‘Date’ and ‘Order’

Id   date        order   amount  Ctr
101  5/20/2020     1       25   1
101  5/20/2020     2       25   2
101  5/20/2020     3        0   0
101  5/21/2020     1       25   1
101  5/21/2020     2       25   2

Code:

Data want:
Set have;
By id date order;
Ctr+1;
If first.id and first.date and first.order) and amount=25 then ctr=1;
Run;

I am not getting the desired result. Any help is appreciated.

Answer 1

If you cannot leverage the order variable for whatever reason, this may work. I suspect if your cases get more complex it may break though.

• Set the counter to 0 at the first of each date or an amount that's not 25.
• Otherwise if the amount is 25 increment the counter.

data have;
input Id $  date mmddyy10.       order   amount;
cards;
101  5/20/2020     1       25
101  5/20/2020     2       25
101  5/20/2020     3        0
101  5/21/2020     1       25
101  5/21/2020     2       25
;;;


data want;
set have;
by id date ;
if first.date or amount ne 25 then ctr=0;
if amount=25 then ctr+1;
run;

proc print data=want;
run;

Results:

Obs Id  date    order   amount  ctr
1   101 22055   1   25  1
2   101 22055   2   25  2
3   101 22055   3   0   0
4   101 22056   1   25  1
5   101 22056   2   25  2